A distinguishing feature of the Oracle server is an alternative storage option, ORGANIZATION INDEX,  which creates index-organized tables (IOTs).  Available since Oracle8, IOTs have proved their worth as they provide faster access and increased availability and scalability to a wide range of applications.  Now with Oracle9i, the benefits of using IOTs are even greater.

Unlike conventional tables, an IOT contains ALL the data -- both the indexed and additional columns -- stored in the primary key index.  This means that once a search has found the key values, it also finds the data located there, thus eliminating  the any additional search for the data itself via a ROWID reference back to a table.  Use of primary key based logical ROWIDs rather than physical ones, in secondary indexes on IOTs provides high availability because they do not become invalid after table reorganization that causes any changes in the underlying table.   Support for partitioning and parallel query operations also enhance availability and allows performance to scale well for large data sets.  Oracle9i IOTs builds on this foundation and contributes its share to the continuous availability and tremendous performance and scalability of the Oracle9i server.  New features include:

• Additional ONLINE operations - Oracle8i introduced many useful online data reorganization features such as creating, rebuilding, and coalescing indexes online and reorganizing IOTs online. Oracle9i further extends these capabilities by adding support for: ONLINE MOVE of an IOT with its OVERFLOW segment; ONLINE CREATE and REBUILD of IOT indexes; ONLINE COALESCE of  IOT indexes; and ONLINE CREATE/REBUILD/COALESCE of key-compressed indexes for IOTs and conventional tables.
  
• Bitmapped secondary indexes for IOTS - Bitmap indexing efficiently merges indexes that correspond to several conditions in a WHERE clause. Rows that satisfy some, but not all, conditions are filtered out before the table itself is accessed, thus improving response time.  A bitmap index on an IOT is similar to that on a heap-organized table except that the ROWIDs used in the bitmap index on an IOT are those of the mapping table as opposed to the base table.  Each mapping table row stores one logical ROWID for the corresponding IOT row and thus, the provides one-to-one mapping between logical ROWIDs of the IOT rows and physical ROWIDs of the mapping table rows.  Because it is logical, moving rows of an IOT does not make it invalid because it can still be accessed by the primary key.
  
• B-tree indexes on the UROWID for IOTs - UROWID (universal row identifier) datatype columns can now hold logical primary key-based ROWIDs identifying rows of an IOT.  Replicated IOTs benefit from indexes on the UROWID column by using this column to locate the row in the snapshot efficiently, thus giving a fast refresh of the snapshot or materialized view for an IOT.
  
• ONLINE Guess-DBA (DataBlock Access) improvements - Secondary indexes on a IOT store the logical ROWID of the base table which contains the guess-DBA of the corresponding primary row at index creation.  Over time, the guess-DBA can get out of synchronization with the actual row and be incorrect.  When this happens, a primary key lookup is required to locate the actual table row in the IOT.  Invalid physical guesses in these indexes can now be fixed online with the help of ALTER INDEX ... UPDATE BLOCK REFERENCES operation.  This online fixing of the guess-DBA optimizes IOT index performance as the physical guess provides a direct access to the index leaf block containing the IOT row. 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.
  
• Additional PARALLEL operations - The CREATE TABLE ... AS SELECT statement creates an IOT and loads it in parallel using the PARALLEL clause in the underlying subquery (AS SELECT). This statement has now been extended to include OVERFLOW segment of an IOT as well.  For range partitioned IOTs, parallel DML is supported.
  
• Additional partitioning operations - The hash partitioning method is provided in addition to the existing range partitioning method.  IOTs created using this method, known as hash partitioned IOTs, are suitable for data sets where partition key ranges can not be used for uniform load balancing across partitions.  Range partitioned IOTs can now contain columns with LOBs.

More Info

Oracle9i Database Documentation

Oracle9i Daily Features

Archives