Oracle indexes can greatly improve query performance but there are some important indexing concepts to understand.
- Index clustering
- Index blocksizes
Indexes and blocksize
Indexes that experience lots of index range scans of index fast full scans (as evidence by multiblock reads) will greatly benefit from residing in a 32k blocksize.
Today, most Oracle tuning experts utilize the multiple blocksize feature of Oracle because it provides buffer segregation and the ability to place objects with the most appropriate blocksize to reduce buffer waste. Some of the world record Oracle benchmarks use very large data buffers and multiple blocksizes.
According to an article by Christopher Foot, author of the OCP Instructors Guide for Oracle DBA Certification, larger block sizes can help in certain situations:
“A bigger block size means more space for key storage in the branch nodes of B-tree indexes, which reduces index height and improves the performance of indexed queries.”
In any case, there appears to be evidence that block size affects the tree structure, which supports the argument that data blocks affect the structure of the tree.
Indexes and clustering
The CBO’s decision to perform a full-table vs. an index range scan is influenced by the clustering_factor (located inside the dba_indexes view), db_block_size, and avg_row_len. It is important to understand how the CBO uses these statistics to determine the fastest way to deliver the desired rows.
Conversely, a high clustering_factor, where the value approaches the number of rows in the table (num_rows), indicates that the rows are not in the same sequence as the index, and additional I/O will be required for index range scans. As the clustering_factor approaches the number of rows in the table, the rows are out of sync with the index.
Oracle MOSC Note:223117.1 has some great advice for tuning-down “db file sequential read” waits by table reorganization in row-order:
– If Index Range scans are involved, more blocks than necessary could be being visited if the index is unselective: by forcing or enabling the use of a more selective index, we can access the same table data by visiting fewer index blocks (and doing fewer physical I/Os).
– If the index being used has a large Clustering Factor, then more table data blocks have to be visited in order to get the rows in each Index block: by rebuilding the table with its rows sorted by the particular index columns we can reduce the Clustering Factor and hence the number of table data blocks that we have to visit for each index block.
This validates the assertion that the physical ordering of table rows can reduce I/O (and stress on the database) for many SQL queries.
Tip! In some cases Oracle is able to bypass a sort by reading the data in sorted order from the index. Oracle will even read data in reverse order from an index to avoid an in-memory sort.
