Question: I noted with Exadata this new
concept of a “storage index”. What is a storage index, and how
does it make Exadata faster than a traditional hardware server?
Answer: Oracle mouthpieces are quite
vocal on selling the million dollars Exadata machines, and there are
some published details on storage indexes.
In a nutshell, the Exadata storage index is a sophisticated data
buffering tool that is tailored for data warehouse full-scan
- Block processing buffer: The traditional
Oracle RAM data buffer (db_cache_size) is designed for fetching
individual block-at-a-time for queries. Rows fetched via index scans are
given priority over rows fetched via large table full table
- Block Set Processing buffer: The Exadata
storage index is designed to track ranges of adjacent data blocks
as they come-in from disk via parallelized full-table scans.
Rows fetched via full-scans are optimized, since the storage
index buffer checks for ranges of adjacent data block instead of
a single data block.
Some experts say that an Oracle storage index is a giant
in-memory structure, a giant RAM buffer divided into “chunks”.
It’s a bit of a misnomer to call this a “storage index”, since
it appears that Exadata stores data block from tables in the storage
index, usually when they are accessed via a full-table scan. A
storage index is not a “real” index in the sense that the storage
index exists only in RAM, and it must be re-created from scratch
when the Exadata server is bounced.
The Exadata documentation notes that
storage indexes are similar to the traditional db_cache_size
data buffer in the sense that Oracle Exadata checks to see if the
requested range of data blocks is already in RAM before performing
an expensive disk I/O.
However, it differs from the traditional RAM data buffer in that
a storage index tracks examines the SQL where clause and
checks to see if the minimum and maximum values are contained in the
storage index. In the traditional Oracle data buffer, checks are
made for individual data blocks.
Also, see my related notes on the Exadata
Oracle smart scan.
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.