Use Oracle 9i Full-index Scans to Read Data Quickly

November 26, 2002

[From Builder.com]

In keeping with Oracle's commitment to add intelligence to SQL query optimization, the full-index SQL execution plan has been enhanced in Oracle9i to provide support for function-based indexes (FBIs). With Oracle8, intelligence was added to the SQL optimizer to determine if a query might be resolved exclusively within an existing index. Oracle's index-organized table (IOT) structure is an excellent example of how Oracle is able to bypass table access whenever an index exists. In an IOT structure, all table data is carried inside the b-tree structure of the index, making the table redundant.

Whenever the Oracle SQL optimizer detects that the query is serviceable without touching table rows, Oracle invokes a full-index scan and quickly reads every block of the index without touching the table itself. It is important to note that a full-index scan does not read the index nodes. Rather, a block-by-block scan is performed and all of the index nodes are quickly cached. Best of all, Oracle invokes multiblock read capability, invoking multiple processes to read the table.

This article continues at http://builder.com.com/article.jhtml?id=u00320021125brl01.htm&page=1&vf=tt.

Back to Database Journal Home