Describe an index-organized table and explain when to use it.
What is index-organized table?
There is nothing extraordinary about an index-organized table, namely the object is exactly what its name implies, it is a table structure organized like a B*-tree index.
Advantages of index-organized table
As with all index structures, the advantages of an index-organized table relate to performance. All the data in an index-organized table is stored in an index structure. Therefore, there is no need to use the index structure to first locate a value and then use the ROWID of the associated row in the table to fetch the data from the row as is normally done with
regular tables and their indexes. This means that the I/O operations required to retrieve data are significantly reduced.
In addition to reducing I/O for retrieval, index-organized tables also eliminate the need to store the ROWID in the leaf nodes of the index-structure. An index-organized table will require less space than an identical index on a standard table because of the lack of ROWIDs, which in turn leads to improved performance.
Limitations of an index-organized table
One limitation of index-organized tables is that they can not be used all the time. Typically, an index contains a subset of the total data in a table. The reason for this stems from the way a B*-tree index is built. A B*-tree index depends, in part, on a small amount of data in the actual index, which in turn reduces the size of the leaf nodes and makes access efficient.
The ideal time to use an index-organized table is when a relatively small amount of data is being accessed by an index entry. Typical applications that fit this profile include:
Information retrieval: Where an index simply contains a pointer to an occurrence of a piece of information. Information retrieval applications are typically used to search for words and phrases in documents.
Spatial data: Where the index contains a locating value, similar to the pointer in an information retrieval application. Spatial data is used in geographic systems.
Online Analytical Processing (OLAP): These applications frequently have a fact table that contains a single numeric value. A data warehouse is a classic OLAP application.
In the next lesson, you will learn how to create index-organized tables.
Understanding the Structure
From a user or developer perspective, an index-organized table (IOT) appears like a normal table. IOTs are stored in a B-tree structure abdhere must be a primary key on an index-organized table, as the data is
stored in primary key order. Since there is no data segment, there is no physical ROWID values for indexorganized tables. See Figure 5 below for an example of an IOT.
Figure 5: Employees represented in an Index Organized Table
IOTs support many of the same features found in heap-organized tables, such as
Constraints: A constraint is a database rule that you define within the database to enforce certain conditions regarding the data that is stored in the database tables