RelationalDBDesign RelationalDBDesign

Index Organized  «Prev 

Reorganizing an index-organized table

Steps to Reorganize Data

Oracle Index-Organized Tables

Oracle (IOTs) Index-organized tables are a unique style of table structure that is stored in a B-tree index structure. Besides storing the primary key values of an Oracle indexed-organized tables row, each index entry in the B-tree also stores the non-key column values.
Oracle Indexed-organized tables provide faster access to table rows by the primary key or any key that is a valid prefix of the primary key. Because the non-key columns of a row are present in the B-tree leaf block itself, there is no additional block access for index blocks.
Applications manipulate, select, and update the Oracle index-organized tables just like ordinary tables and that is by means of SQL statements.

LIST Partition for Oracle Index Organized tables

In the previous database release, you could partition an Oracle index-organized table by range or by a hash on column values. The partitioning columns had to form a subset of the primary key columns. Just like ordinary tables, local partitioned (prefixed and non-prefixed) indexes as well as global partitioned (prefixed) indexes were supported for partitioned Oracle index-organized tables. With the release of Oracle 10g, you can partition by the list method.
IOT - Oracle Index-organized tables: These structures remove the primary key from the table and keep them solely in the index, saving disk space. Primarily used with high-updates tables, the Index Organized tables structure reduces table fragmentation.
Accessing data by means of the primary key is quicker because the key and the data reside in the same structure, and there is no need to read an index then read the tables data in a separate structure.
Lack of duplication of the key columns in an index and table mean the total storage requirements are reduced.
Index-only tables have been around since Oracle8. If neither the Oracle HASH or Oracle INDEX ORGANIZED options are used with the CREATE TABLE command, then a table is created as a standard hash table.
If the Oracle INDEX ORGANIZED option is specified, the table is created as a B-tree-organized table identical to a standard Oracle index created on similar columns. Furthermore, index-organized tables do not have rowids.
Oracle index-organized tables have the option of allowing overflow storage of values that exceed optimal index row size, as well as allowing compression to be used to reduce storage requirements. Overflow parameters can include columns and the percent threshold value to begin overflow. An index-organized table must have a primary key and are best suited for use with queries based on primary key values. Oracle index-organized tables can be partitioned in Oracle8i and in Oracle9i as long as they do not contain LOB or nested table types.

The first step is to export the data from the index-organized table.

The second step is to completely drop the table from the database.

The third step is to create the table.

The final step is to reload the data back into the table.