Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

Database News

Posted Nov 26, 2002

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

By DatabaseJournal.com Staff

[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

Database News Archives

Latest Forum Threads
Database News Forum
Topic By Replies Updated
Efficient SQL Server Indexing by Design lcole 0 April 30th, 12:38 PM
Mine Oracle Database, SQL Server and Other Databases with Monarch Data Pump Pro V10.5 lcole 0 April 30th, 12:37 PM
Oracle Database and Oracle Fusion Middleware for Private Social Network Application lcole 0 April 30th, 12:31 PM
Oracle Database Maintains a Stronghold in the DBMS Market lcole 0 April 30th, 12:30 PM