Performance Tuning  «Prev  Next»

Lesson 7

Index-organized Tables Conclusion

Index-organized tables are a blend of a standard index and a standard table. In the right circumstances, such as times when a table contains only small amounts of data besides the primary key, an index-organized table can deliver high performance. However, there are also some significant limitations on the use of an index-organized table.
This module discussed various element regarding index-organized tables, including how to:
  1. Describe an index-organized table and explain when to use it
  2. Create an index-organized table
  3. Rebuild an index-organized table with the MOVE parameter of the ALTER TABLE command
  4. Create a secondary index on index-organized table
  5. Understand the differences between index-organized and regular tables
The following terms were defined in this module:

  1. Online Analytical Processing (OLAP): A type of computing that delivers summary data organized by a variety of attributes, such as time or location. OLAP is used a lot with data warehouses.
Online Analytical Processing (OLAP): A type of computing that delivers summary data organized by a variety of attributes, such as time or location. OLAP is used a lot with data warehouses.

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.

Creating Index-Organized Tables

You use the CREATE TABLE statement to create index-organized tables, but you must provide additional information:
  1. An ORGANIZATION INDEX qualifier, which indicates that this is an index-organized table
  2. A primary key, specified through a column constraint clause (for a single column primary key) or a table constraint clause (for a multiple-column primary key).
Optionally, you can specify the following:
  1. An OVERFLOW clause, which preserves dense clustering of the B-tree index by enabling the storage of some of the nonkey columns in a separate overflow data segment.
  2. A PCTTHRESHOLD value, which, when an overflow segment is being used, defines the maximum size of the portion of the row that is stored in the index block, as a percentage of block size. Rows columns that would cause the row size to exceed this maximum are stored in the overflow segment. The row is broken at a column boundary into two pieces, a head piece and tail piece. The head piece fits in the specified threshold and is stored along with the key in the index leaf block. The tail piece is stored in the overflow area as one or more row pieces. Thus, the index entry contains the key value, the nonkey column values that fit the specified threshold, and a pointer to the rest of the row.
  3. An INCLUDING clause, which can be used to specify the nonkey columns that are to be stored in the index block with the primary key.

Index Organized Tables - Quiz

Click the Quiz link below to test your understanding of the concepts presented in this module.
Index Organized Tables - Quiz
In the next module, you will learn about Advanced Queuing.