An Index-Organized Table (IOT) is a unique storage organization feature of the Oracle9i server that provides added value in performance, scalability, and availabili ty over conventional tables.  The actual data for an index-organized table is stored in B-tree index leaves in sorted order based on the table's primary key so that changes to that data, such as adding, updating, or deleting rows, require an update t o the index only.  This approach enables extremely fast access to table data for primary key based queries, making index-organized tables ideal for a wide variety of applications. 

Where are Index-Organized Tables Used?

The answer is simple -- The  demand for ever faster query performance, high availability, and reduced storage make in dex-organized tables ideal choice for a variety of applications that use data sets with individual rows identified by a primary key.  Example applications span a wide range including:

• Electronic order processing - An index-organized table is an ideal storage structure for the Îordersâ table, when the query and DML is predominantly primary-key based.  Heavy volume of DML operations occurring in this type of application usually fra gments the table, requiring frequent table reorganization. An index-organized table can be reorganized without invalidating its secondary indexes, and can be performed online  thus reducing or even eliminating "downtime" for the orders table.
• Electronic catalogs - An index-organized table can be used to store all types of manufacturing and retail catalogs.  Manufacturing catalogs are usually indexed by product attributes based o n primary key and a retailers catalog may have a multi-column primary key matching the hierarchy of products offered.  Both types benefit from using index-organized tables.  Key compression can be used on these index-organized tables to avoid co lumn value repetitions increasing performance and reducing storage. 
• Internet searches - These applications maintain lists of keywords, users, or URLs, suitable for storage in a inde x-organized table, where each row holds a primary key with some additional information.  An index-organized table storing URLs and their associated links can considerably speed up access time.
• Web portals and auction sites - A prevailing feature of these application types is databases of users names with a subset of this available user information accessed more frequently than the rest.  The flexible column placement within index-organized tables provides options for increasing performance of these applications.
• Data Warehousing - Index-organized tables support parallel features for loading, index creation, and scans required for handling large volumes of data.  Partitioned index-organized tables are also supported, so that  each partition can be loaded concurrently.  Data warehousing applications using star schemas can also gain performance and s calability by implementing "fact" tables as index-organized tables for efficient execution of star queries. All these features make index-organized tables suitable for handling large scale data.

Reduced Storage 
Index-organized tables maintain a single storage structure -- the B-tree index.  Primary key column values are stored only in the B-tree ind ex and not duplicated in the table and index as happens in a conventional heap-organized table.  Because rows of an index-organized table are stored in primary key order, a significant amount of additional storage space savings can be obtained throug h the use of key compression. 

Increased 24x7 Availability 
Index-organized tables identify rows using logical ROWIDs based on the primary key. The use of logical ROWIDs enables online reorganization and also does not affect the secondary indexes which remain valid and usable after the reorganization.  This capability reduces or eliminates the downtime for reorganization of secondary indexes, making index-organized tables beneficial for 24x7 applications. 

Increased Scalability
Index-organized tables are highly scalable because of their support for partitioning and parallel operations. Range partitioned and hash partitioned index-organized tables as well as LOB columns in partitioned index-organized tables are supported.  Parallel query and parallel DML are also supported.  Queries on index-organized tables including partitioned ones use the cost-based optimizer for generating optimal execution flow.

Easy to Use AND Fully Functional
Applications manipulate the index-organized table just like an ordinary table via standard SQL statements.  All Oracle programmatic interfaces including PL/SQL, OCI, and  JDBC can access index-organized tables. The full complement of Oracle utilities and tools are supported including the SQL*Loader (both conventional and direct path), IMPORT/EXPORT, and transportable tablespace support for point-in-time recovery operations using the Oracle Recovery Manager (RMAN).

Index organized tables support the standard featu res available with conventional tables -- secondary indexes, constraints, triggers, composite columns, object and REF columns, and LOB columns. Key compression is supported on secondary indexes and the index-organized table as it is itself stored in an in dex. An index-organized table can also be used as an object table and can be replicated. From the perspective of security, administration, and tools, index-organized tables are treated in the same fashion as conventional tables.

 

KEY FEATURES
Fu ll Table Functionality CREATE TABLE AS SELECT ALTER TABLE OPTIONS Logical ROWID support Secondary Index LOB columns Constraints  Triggers Horizontal partitioning Distributed database and replication support NEW! Extensible Indexing support IMPORT/EXPORT SQL Loader support Conventional  Direct Path Object Support Object Type VARRAYS Nested Tables REF columns Performance Features OVERFLOW segment support Key compression NEW! Bitmap secondary indexes on non-partitioned IOTs NEW! Online Guess-DBA fixing for secondary indexes Column placement options PCTTHRESHOLD  INCLUDING Scalability Features Range Partit ioning NEW! Hash partitioning NEW! LOB columns in partitioned IOTs  Parallel query features NEW! Parallel DML features Support for Materialized views Availability Features ONLINE MOVE Online reorganization Logical secondary indexes NEW! ONLINE CREATE and REBUILD of indexes NEW! ONLINE COALESCE of indexes NEW! ONLINE MOVE of OVERFLOW segments NEW! ONLINE CREATE/REBUILD/COALESCE of key compressed indexes