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:
- 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.
- 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.
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;