Lesson 8

Index-organized tables can be a nice performance enhancer for the right situation. In this module, you learned about index-organized tables, including:

1. The benefits of index-organized tables
2. How to create an index-organized table
3. How to create secondary indexes on an index-organized table
4. How to handle large rows in an index-organized table
5. How to delete and modify index-organized tables
6. How to reorganize an index-organized table

The simplest explanation of an index-organized table is that it is accessed like any other Oracle table (typically a heap-organized table) but is physically stored like an Oracle B-tree index. Index-organized tables are typically created on "thin" tables (tables without too many columns). Typically, multiple columns of the table make up the primary key of the index-organized table. The non-key columns can also be stored as part of the B-tree index. The proper configuration and use of index-organized tables is fairly specific and does not meet all application needs.

From a user or developer perspective, an index-organized table (IOT) appears like a normal table. IOTs are stored in a B-tree structure. There 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 4 for an example of an IOT.

Because all of the data within an index-organized table is stored within the index itself, there are physical differences in the way an index-organized table is stored, as compared to a normal B-tree index that supports a normal heap-organized table. Some of the unique aspects of IOT's are as follows:

1. Secondary indexes use logical ROWIDs rather than physical ROWIDs.
2. They require a primary key.
3. Primary key compression can be used to save storage and reduce size of an IOT.
4. An overflow segment can be used for non-key column data.
5. Secondary bitmap indexes require a defined mapping table.
6. Non-key column data is stored in the leaf blocks of an IOT.

There are limitations on index-organized tables, although many of the limitations will not affect their use in the majority of applications. Some of these limitations include:

1. Rows exceeding 50% of a block must use an overflow segment.
2. IOTs cannot use virtual columns.
3. Tables with more than 255 columns must have an overflow segment.
4. Tables cannot have more than 1,000 total columns.
5. The primary key cannot be more than 32 columns.

In this module, you were introduced to the following glossary term:

1. online analytical processing

In the next module, you will learn about Oracle’s auditing capabilities.

Click the Quiz link below to test your knowledge of using index-organized tables.
Index Organized Tables - Quiz