Databases Reference
In-Depth Information
From this query, you get the following information:
•
The table name
•
The index name(s), which includes the primary key and any secondary indexes on
the table
•
The index type, which will be designated as 'IOT - TOP' for index-organized tables
The
PCTTHRESHOLD
for the table
•
The
INCLUDING
column, if specified
You need to do an outer join to the
USER_TAB_COLUMNS
view in order to get the column name for the
column specified by the
INCLUDING
clause, which is optional when creating an index-organized table.
The
COLUMN_ID
column on the
USER_INDEXES
view specifies the column number of the column for the
INCLUDING
clause. If there is no
INCLUDING
clause specified on the index-organized table, the
COLUMN_ID
column will be populated with a default value of '0' or it will be populated with the value from the
USER_TAB_COLUMNS COLUMN_ID
column.
If you look at the
USER_TABLES
view, both the IOT itself and the overflow segment are shown.
•
SQL> select table_name, iot_type, segment_created from user_tables;
TABLE_NAME IOT_TYPE SEG
------------------------------ ------------ ---
SYS_IOT_OVER_77704 IOT_OVERFLOW YES
EMPLOYEES_IOT IOT YES
If querying
DBA_SEGMENTS
to get actual physical characteristics of the IOT itself, as well as the
overflow segment, remember to use the primary key segment_name; the table name itself will not be
specified within the
DBA_SEGMENTS
view, since the IOT is essentially an index segment.
1 select segment_name, segment_type
2 from dba_segments
3* where segment_name like '%IOT%'
SQL> /
SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------
SYS_IOT_OVER_77704 TABLE
EMPLOYEES_IOT_PK INDEX
Summary
Index-organized tables have a specific niche in applications and are not really suitable for extensive use.
The guidelines to see if a table is a good candidate for an IOT include:
•
Is it a table with a small number of columns?
•
Is it a table made up of a composite primary key (several columns of the table)?
•
Does the table require fast primary key access?
