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 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
1050 Wilshire Drive,
Suite 170,
Troy, MI 48084
Phone: (248) 465-9533
Toll free: 1-877-868-1753
Email: [email protected]
© Centroid, Inc. All rights reserved. Contact Privacy Policy Terms of Use CCPA Policy
Centroid is a cloud services and technology company that provides Oracle enterprise workload consulting and managed services across Oracle, Azure, Amazon, Google, and private cloud. From applications to technology to infrastructure, Centroid’s depth of Oracle expertise and breadth of cloud capabilities helps clients modernize, transform, and grow their business to the next level.
© Centroid, Inc. All rights reserved. Contact Privacy Policy Terms of Use CCPA Policy