Advantages and Disadvantages of Oracle index-organized Tables

The index-organized table has a number of advantages and disadvantages associated with it.

1. Advantages of index-organized Tables There are three main advantages to using an index-organized table:
   • Increased performance: There is no need to access a row in the database from an index structure, so you can reduce the total number of I/O operations needed to retrieve data.
   • Reduced table space: Because you do not need to link to a row in a table, there is no need to store the ROWID in the index. The overall space required for the table is reduced.
   • Presorted data: The data in the leaf nodes is already sorted by the value of the primary key.
2. Disadvantages of index-organized Tables: Saving space and eliminating I/O operations sound like great ways to enhance performance in your relational database management system, and they are. But index-organized tables also have disadvantages.
   • You must have a primary key on the table with a unique value.
   • You cannot have any other indexes on the data.
   • You cannot partition an index-organized table.
   • An index-organized table cannot be a part of a cluster.
   The data in the row must be fairly small. Large amounts of data in the leaf nodes will require more I/Os to retrieve it, compromising some of the advantages of the index-organized table.

As with any feature, index-organized tables are best used in situations where their advantages greatly outweigh the potential disadvantages. As we have seen, they are most effective in tables where the data in the rows is fairly small. Applications where index-organized tables are appropriate include the following:

1. Information retrieval: An information retrieval application essentially creates an index for items in another type of data, such as an application that searches for text strings in a document. Tables that support these applications typically contain a token that points to an occurrence of a piece of data in another document, which is small enough to leverage the advantages of an index-organized table.
2. Spatial data: Like data used for information retrieval, spatial data usually has a locating index and a token that points to another type of data, so it is very appropriate for an index-organized table.
3. (OLAP) Online analytical processing: Online analytical processing applications frequently have fact tables that contain a relatively small amount of data, such as numeric totals, which make them ideal for using index-organized table structures.

There are specific advantages of IOTs, including the following:

1. Storage space can be saved because the data is the index, so there is only one segment or set of segments in the database for an index-organized table, rather than the normal two segments that come with a heap-organized table and associated index(es).
2. Query performance benefits can occur because there are less I/O requirements. Since the data is stored as part of the index, there is a potentially significant I/O reduction.
3. DML performance benefits can occur because there is only the need to update the index segment(s), as there is no data segment(s) as part of the structure. There is no need to update the table and then any associated index as with heap-organized tables. Only the index needs to be updated.

Index-organized tables are most beneficial in OLTP^[1] environments for the following reasons:

1. IOTs allow fast primary key access.
2. They allow online reorganization, which is essential in an OLTP environment.
3. IOTs allow fast data retrieval in applications such as Internet search engines.

The biggest challenge with index-organized tables is deciding when to use them. If you have tables that have several columns that make up the primary key, and the table itself is not dense as far as number of columns, it may be a candidate as an IOT. However, this by itself is not reason enough to make a table into an index-organized table. There should be a tangible benefit gained from having a table structure be index-organized, and this may require some testing of your application. Generally, index-organized tables provide fast lookup of the primary key. They can be slower for inserts. Likewise, secondary index access isn't as fast as a normal B-tree index because index-organized table rows do not have the physical ROWID that would be found in a heap-organized table.
Instead, IOTs use a logical ROWID, which isn't as exact as a physical ROWID and can become outdated over time. All in all, the use of index-organized tables should be limited and specific to a particular need. They are best used when fast primary key access is required.

Pushing to the extreme the principle of storing as much data as possible in the indexes, some database management systems, such as Oracle, allow you to store all of a table's data into an index built on the primary key, thus getting rid of the table structure altogether. This approach saves storage and may save time. The table is the index, and is known as an (IOT) index-organized table as opposed to the regular heap structure.
In the next lesson, you will learn how to create an index-organized table.

Click the Quiz link below to test your knowledge of index-organized tables.
Advantages Disadvantages Indexed Tables- Quiz

[1]OLTP : OLTP is an operational system that supports transaction-oriented applications in a 3-tier architecture. It administers the day to day transaction of an organization.