RelationalDBDesign RelationalDBDesign 


Performance Tuning  «Prev 

Creating a Secondary Index on an index-organized Table in Oracle

You can create secondary indexes on an index-organized tables to provide multiple access paths. Secondary indexes on index-organized tables differ from indexes on ordinary tables in two ways:
  1. They store logical rowids instead of physical rowids. This is necessary because the inherent movability of rows in a B-tree index results in the rows having no permanent physical addresses. If the physical location of a row changes, its logical rowid remains valid. One effect of this is that a table maintenance operation, such as ALTER TABLE ... MOVE, does not make the secondary index unusable.
  2. The logical rowid also includes a physical guess which identifies the database block address at which the row is likely to be found. 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.
Unique and non-unique secondary indexes, function-based secondary indexes, and bitmap indexes are supported as secondary indexes on index-organized tables.

Creating a Secondary Index on an Index-Organized Table

The following statement shows the creation of a secondary index on the docindex index-organized table where doc_id and token are the key columns:
CREATE INDEX Doc_id_index on Docindex(Doc_id, Token);

This secondary index allows the database to efficiently process a query, such as the following, the involves a predicate on doc_id:
SELECT Token FROM Docindex WHERE Doc_id = 1;

CREATE INDEX index_name ON column_list;
CREATE INDEX Required keywords.
index_name The name of the index you will create
ON Required keyword
column_list A list of columns in the index, separated by columns