Oracle Storage Indexes

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 queries.

• 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 scans.
  
  
• Block Set Processing buffer:  The Exadata storage index is designed to track ranges of adjacent data blocks (usually time-based) 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.