An index-organized table keeps its data sorted according to the primary key column values for the table. An index-organized table stores its data as if the entire table was stored in an index. Indexes serve two main purposes:
- To enforce uniqueness When a PRIMARY KEY or UNIQUE constraint is created, Oracle creates an index to enforce the uniqueness of the indexed columns.
- To improve performance When a query can use an index, query performance may dramatically improve.
An index-organized table allows you to store its entire data in an index. A normal index only stores the indexed columns; an index-organized table stores all its columns in the index.
To create an index-organized table, use the ORGANIZATION INDEX clause of the CREATE TABLE command, as shown in the following example:
To create TROUBLE as an index-organized table, you must create a PRIMARY KEY constraint on it.
An index-organized table is appropriate if you will always be accessing the TROUBLE data by the CITY and SAMPLE_DATE columns (in the WHERE clauses of your queries). To minimize the amount of active management required for the index, you should use an index-organized table only if the table’s data is very static. If the table’s data changes frequently, you should use a regular table with indexes as appropriate.
In general, an index-organized table is most effective when the primary key constitutes a large part of the table’s columns. If the table contains many frequently accessed columns that are not part of the primary key, the index-organized table will need to access its overflow area repeatedly. Despite this drawback, you may choose to use index-organized tables to take advantage of a key feature that is not available with standard tables: the ability to use the MOVE ONLINE option of the ALTER TABLE command. You can use that option to move a table from one tablespace to another while it is being accessed by INSERT, UPDATE, and DELETE operations. The only other option for moving tables while allowing DML is to use the DBMS_REDEFINITION package, but that is not as easy to use and incurs a lot of overhead to maintain the table changes while moving the rest of the table to another tablespace. You cannot use the MOVE ONLINE option for partitioned index-organized tables.
Leave a Reply