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/O’s (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/O’s to sweep the table in index order.

As we may know, it is the physical I/O’s that impede performance, not the logical I/O’s. But how do we translate 4,000,000 logical I/O’s into the number of physical I/Os? Let’s 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/O’s to access each table data block).

Now, let’s 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, let’s 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.