4.9 How do I…Create an index-organized table?Problem
I want to improve my performance when accessing my table that contains a primary key. I also want to save storage space by creating my table as an index-organized table. How do I create an index-organized table?
Technique
An index-organized table is created by using the ORGANIZATION INDEX clause of the CREATE TABLE command. The basic syntax to create an index-organized table is shown in Figure 4.25.
All index-organized tables are stored in the B-tree structure based on the primary key. This structure is used for Oracle indexes. The difference is that non-key columns are stored in the B-tree structure. Thus, queries and updates to the table based on the primary key are improved by using this object format.
A full table scan will return records in the order of the primary key, much like a clustered table.
Steps
1. Connect to SQL*Plus as the Waite user account. Run the CHP4_27.SQL script, shown in Figure 4.26, to create a sample index-organized table.
SQL> START CHP4_27.sql
Table Created.
How It Works
Note that the TEMP tablespace is not usually used for overflow tablespaces. For simplicity, TEMP was used; it is recommended to have an additional tablespace for overflow data.
The ORGANIZATION INDEX clause of the CREATE TABLE command will create an index-organized table. CHP4_27.SQL shows a simple create statement.
The PCTTHRESHOLD clause specifies what percentage of the block should be reserved for a record. If the record is larger than that defined in PCTTHRESHOLD, the portion of the record exceeding the threshold will be stored in the tablespace defined in the OVERFLOW clause. If no OVERFLOW clause is specified, all rows that contain even a portion exceeding the PCTTHRESHOLD are rejected completely and are not inserted into the index-organized table.
The INCLUDING clause, if specified, determines the column that will divide the table into the index portion and the overflow portion. Each column after the INCLUDING column is stored in the overflow portion of the index-organized table.
Storage is reduced, because the primary key becomes part of the table and is not stored as a separate index, and ROWIDs are not stored as they are in primary keys.
Comments
Index-organized tables may not contain a column of the LONG datatype. They may not have constraints (except for the primary key), nor any additional indexes.
As of Oracle8.0.3, index-organized tables may not be clustered, replicated, partitioned, or distributed.
Each index-organized table must contain a primary key. The primary key uniquely defines a record; ROWIDs are not used.
The PCTTHRESHOLD clause, if defined, must be 0 to 50.