The index-organized table has a number of advantages and disadvantages associated with it.
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.
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:
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.
- 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.
OLAP: 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.
In the next lesson, you will learn how to create an index-organized table.
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.
Click the Quiz link below to test your knowledge of index-organized tables.
Advantages Disadvantages Indexed Tables- Quiz