BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

Oracle Index Access Methods

Oracle Tips by Burleson Consulting

As you may know, Oracle offers a variety of indexing methods including b-tree, bitmapped, and function-based indexes. Regardless of the index structure, an Oracle index can be thought of as a pair bond of a symbolic key and a ROWID. The goal of index access is to gather the ROWIDs required to quickly retrieve the desired table rows. Within Oracle, we see the following types of index access.

  • Index range scanThis is the retrieval of one or more ROWIDs from an index. Indexed values are generally scanned in ascending order.

  • Index unique scanThis is the retrieval of a single ROWID from an index.

  • Descending index range scanThis is the retrieval of one or more ROWIDs from an index. Indexed values are returned in descending order.

  • And-equal filterThis is an operation that gathers multiple sets of ROWIDs from the where clause of a query (e.g., select customer_name from customer where status = ‘OPEN’ and age > 35;). The and-equal operation compares the sets of ROWIDs and returns the intersection of these sets, thereby eliminating duplicates and satisfying the and conditions in the where clause.

Index Range Scan

The index range scan is one of the most common access methods. During an index range scan, Oracle accesses adjacent index entries and then uses the ROWID values in the index to retrieve the table rows (see Figure 3-5).

Figure 5: An index range scan

An example of an index range scan would be the following query.

select
   employee_name
from
   employee
where
   home_city = ‘Rocky Ford’;

Tip: Because an index range scan fetches the ROWID list from the index, each ROWID will most likely point to a different data block causing a disk I/O for each block in the index range scan. In practice, many Oracle SQL tuning professionals will resequence the table rows into the same physical order as the primary index. This technique can reduce disk I/O on index range scans by several orders of magnitude. For details, see "Turning the Tables on Disk I/O" by Don Burleson in the January/February 2000 issue of Oracle Magazine online.

Oracle provides a column called clustering_factor in the Remote DBA_indexes view that tells you how synchronized the table rows are with your index. When the clustering factor is close to the number of data blocks, the table rows are synchronized with the index. As the clustering_factor approaches the number of rows in the table, the rows are out of sync with the index.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter