Index Organized  «Prev  Next»

Lesson 8

Index Organized Tables Conclusion

Performance Enhancement

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

Index-Organized Tables

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.

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. 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.

Index Organized Table
Figure 4: Structure of an index-organized table

IOTs support many of the same features found in heap-organized tables, such as
  1. Constraints
  2. Partitioning
  3. Triggers
  4. LOB columns
  5. Parallelism
  6. Indexes (e.g. secondary indexes on IOTs)
  7. Global hash-partitioned indexes
  8. Online reorganization
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.

Glossary

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.

Index Organized Tables - Quiz

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

Indexing using Oracle Database