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

 

 


 

 

 

 

 

 
 

Clustered Indexes

Oracle Tips by Burleson Consulting

The Data Warehouse Development Life Cycle

Oracle Features for the Data Warehouse
Clustered Indexes

This is a typical index for an Oracle data warehouse. We see that the index has a 3 level index tree with a bit over one million rows and distinct keys. This means that Oracle would perform four logical I/Os (three through the index and one to the data block) to retrieve each row when the index is scanned. Therefore, a 1,000,000 row table would require 4,000,000 logical I/Os to sweep the table in index order.

As we may know, it is the physical I/Os that impede performance, not the logical I/Os. But how do we translate 4,000,000 logical I/Os into the number of physical I/Os? Lets assume that the index requires 4,000 data blocks and that the index resides in contiguous data blocks, but that the customer table is not stored in last_name sequence. This means that as we read each bottom level node of the index, we are unlikely to find that the data block is already in our buffer, since the table is ordered in a different sequence than our index. (figure 8.6)

Figure 8.6 Physical I/O for a non-clustered index

If our db_block_buffers value is large enough to hold the entire index, then we can assume that the entire sweep of the customer table would take 40,000 physical I/Os, (4,000 to read the index, plus 1,000,000 I/Os to access each table data block).

Now, lets examine the difference when the data table is stored in the same sequence as the index. Assume that you have an index that is used more frequently than all of your other indexes. For example, lets say that you have indexed on your customer_last_name (last_name_idx), and this index is used for more than 90% of your database queries.


This is an excerpt from "High Performance Data Warehousing".
If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.

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.