Managing Index-Organized Tables
What Are Index-Organized Tables?
An index-organized table has a storage organization that is a variant of a primary B-tree. Unlike an ordinary (heap-organized) table whose data is stored as an un ordered collection (heap), data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner. Each leaf block in the index structure stores both the key and non key columns.
The structure of an index-organized table provides the following benefits:
■Fast random access on the primary key because an index-only scan is sufficient. And, because there is no separate table storage area, changes to the table data(such as adding new rows, updating rows, or deleting rows) result only in updating the index structure.
■Fast range access on the primary key because the rows are clustered in primary key order.
■Lower storage requirements because duplication of primary keys is avoided. They are not stored both in the index and underlying table, as is true with heap-organized tables.
Index-organized tables have full table functionality. They support features such as constraints, triggers, LOB and object columns, partitioning, parallel operations, online reorganization, and replication. And, they offer these additional features:
■Overflow storage area and specific column placement
■Secondary indexes, including bitmap indexes.
Index-organized tables are ideal for OLTP applications, which require fast primary key access and high availability. Queries and DML on an orders table used in electronic order processing are predominantly primary-key based and heavy volume causes fragmentation resulting in a frequent need to reorganize. Because an index-organized table can be reorganized online and without invalidating its secondary indexes, the window of unavailability is greatly reduced or eliminated. Index-organized tables are suitable for modeling application-specific index structures.
Example: Creating an Index-Organized Table
The following statement creates an index-organized table:
CREATE TABLE admin _doc index(
doc _id NUMBER,
token _frequency NUMBER,
token _offsets VARCHAR2(2000),
CONSTRAINT pk _admin _doc index PRIMARY KEY (token, doc _id))
TABLE SPACE admin _tbs
PCT THRESHOLD 20
OVERFLOW TABLE SPACE admin_tbs2;
Restrictions for Index-Organized Tables
The following are restrictions on creating index-organized tables.
■The maximum number of columns is 1000.
■The maximum number of columns in the index portion of a row is 255, including both key and nonkey columns. If more than 255 columns are required, you must use an overflow segment.
■The maximum number of columns that you can include in the primary key is 32.
■PCT THRESHOLD must be in the range of 1–50. The default is 50.
■All key columns must fit within the specified threshold.
■If the maximum size of a row exceeds 50% of the index block size and you do not specify an overflow segment, the CREATE TABLE statement fails.
■Index-organized tables cannot have virtual columns.
Maintaining Index-Organized Tables
Index-organized tables differ from ordinary tables only in physical organization. Logically, they are manipulated in the same manner as ordinary tables. You can specify an index-organized table just as you would specify a regular table in INSERT,SELECT, DELETE, and UPDATE statements.
Moving (Rebuilding) Index-Organized Tables
Because index-organized tables are primarily stored in a B-tree index, you can encounter fragmentation as a consequence of incremental updates. However, you can use the ALTER TABLE…MOVE statement to rebuild the index and reduce this fragmentation.
The following statement rebuilds the index-organized table admin _doc index:
ALTER TABLE admin _doc index MOVE;
You can rebuild index-organized tables online using the ONLINE keyword. The overflow data segment, if present, is rebuilt when the OVERFLOW keyword is specified. For example, to rebuild the admin _doc index table but not the overflow data segment, perform a move online as follows:
ALTER TABLE admin _doc index MOVE ONLINE;
To rebuild the admin _doc index table along with its overflow data segment perform the move operation as shown in the following statement. This statement also illustrates moving both the table and overflow data segment to new table spaces.
ALTER TABLE admin _doc index MOVE TABLE SPACE admin_tbs2OVERFLOW TABLE SPACE admin_tbs3;
In this last statement, an index-organized table with a LOB column (CLOB) is created. Later, the table is moved with the LOB index and data segment being rebuilt and moved to a new table space.
CREATE TABLE admin _iot _lob
(c1 number (6) primary key,
Admin _lob CLOB)
LOB (admin _lob) STORE AS (TABLE SPACE admin_tbs2);
ALTER TABLE admin _iot_lob MOVE LOB (admin_lob) STORE AS (TABLE SPACE admin_tbs3);
Creating Secondary Indexes on Index-Organized Tables
You can create secondary indexes on index-organized tables to provide multiple access paths. Secondary indexes on index-organized tables differ from indexes on ordinary tables in two ways:
■They store logical row ids instead of physical row ids. This is necessary because the inherent movability of rows in a B-tree index results in the rows having no permanent physical addresses. If the physical location of a row changes, its logical row id remains valid. One effect of this is that a table maintenance operation, such as ALTER TABLE … MOVE, does not make the secondary index unusable.
■The logical row id also includes a physical guess which identifies the database block address at which the row is likely to be found. If the physical guess is correct, a secondary index scan would incur a single additional I/O once the secondary key is found. The performance would be similar to that of a secondary index-scan on an ordinary table.
Analyzing Index-Organized Tables
Just like ordinary tables, index-organized tables are analyzed using the DBMS _STATS package, or the ANALYZE statement.
Collecting Optimizer Statistics for Index-Organized Tables
To collect optimizer statistics, use the DBMS_STATS package. For example, the following statement gathers statistics for the index-organized countries table in the hr schema:
EXECUTE DBMS_STATS.GATHER_TABLE_STATS (‘HR’,’COUNTRIES’);
The DBMS_STATS package analyzes both the primary key index segment and the overflow data segment, and computes logical as well as physical statistics for the table.
■The logical statistics can be queried using USER_TABLES, ALL_TABLES or DBA_TABLES.
■You can query the physical statistics of the primary key index segment using USER_INDEXES, ALL_INDEXES or DBA_INDEXES (and using the primary key index name). For example, you can obtain the primary key index segment physical statistics for the table admin _doc index as follows:
SELECT LAST_ANALYZED, B LEVEL,LEAF_BLOCKS, DISTINCT_KEYS
FROM DBA_INDEXES WHERE INDEX_NAME= ‘PK_ADMIN_DOC INDEX’;
■You can query the physical statistics for the overflow data segment using the USER_TABLES, ALL_TABLES or DBA_TABLES. You can identify the overflow entry by searching for IOT_TYPE = ‘IOT_OVERFLOW’. For example, you can obtain overflow data segment physical attributes associated with the admin _doc index table as follows:
SELECT LAST_ANALYZED, NUM_ROWS, BLOCKS, EMPTY_BLOCKS
FROM DBA_TABLES WHERE IOT_TYPE=’IOT_OVERFLOW’
and IOT_NAME= ‘ADMIN_DOC INDEX’;