Exadata Storage Indexes, Bind Variables, and NULLS

Storage Indexes are a feature unique to Oracle Exadata. A storage index is a memory-based structure that reduces the amount of physical IO required by the cell. A storage index keeps track of minimum and maximum values from query predicates and builds storage index regions based on usage.

The example above shows that for a given table, T, the values for column B are tracked such that in the example where B<2 is provided, Oracle will know to use the storage index in track 1 and refer only to blocks in the B: 1/5 range. The goal is storage indexes, again, is to eliminate unnecessary IO calls to cells and ASM AUs/disks/cells on which the requested data does not exist. The way it’s physically laid out is:

• The storage space inside each cell is logically divided into 1Mb chunks called “storage regions”.
• The boundaries of ASM allocation units (AUs) are aligned with the boundaries of the storage regions.
• In each storage region, data distribution statistics are maintained in a memory structure called a region index.
• Each region index contains distribution information for up to 8 columns.
• The storage index is a collection of region indexes.

The storage statistics represent the data distribution of columns that are considered “well-clustered” by Exadata, and Exadata provides logic to determine what columns are clustered well enough to be included in a storage index. Storage indexes work best when data is roughly ordered so the same column values are clustered together, and when the query predicates contain =, <, >, or some combination of these. The only way to influence the user of a storage index is to load your tables with sorted data.

Test Data

The tests below are done on Centroid’s Exadata X2-2 Quarter Rack Database Machine. We’ve created a 38 million-row table called MYOBJ, which looks like DBA_OBJECTS. We’ve then created MYOBJ_ORDER as a copy of MYOBJ ordered by STATUS, then OBJECT_TYPE. We may have to re-visit this test case at some point, but for now we’ll test with these tables.

Testing whether Storage Indexes Work with Bind Variables

We’ll conduct a simple test with our MYOBJ_ORDER table, which is ordered by STATUS and OBJECT_TYPE (in that order):

Clearly, bind variables are no problem for storage indexes.

Testing whether Storage Indexes Work with NULL values

Let’s take our MYOBJ_ORDER table and make several STATUS columns NULL, and run a similar test:

From the above, we can see that NULL values are allowable for storage indexes.

Summary

• Storage indexes work with bind variables
• Storage indexes work with NULL values