Reorganizing an index-organized table

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.

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.