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 LIKE, BETWEEN, and Wild Cards with Storage Indexes
First, let’s test a query with a wild-card and LIKE in the predicate:
From this testing, it appears as if wild cards are not storage index eligible. It’s interesting to note that using LIKE will pick up a storage index, if the table is sorted on the predicate column, as long as wild cards are not in play – in reality this is probably just an academic exercise because LIKE is rarely used in predicates without wild carding.
Now let’s test BETWEEN in the predicate:
The test above show some interesting things – storage indexes will be used for BETWEEN operations depending on the size of the data requested from the cell. In the case of a predicate between ‘V’ and ‘Z’ or ‘VALID’ and ‘Z’, which is the majority of the 38 million rows, storage indexes are not used. But in cases when the predicate includes ‘INVALID’ rows and excludes NULL and VALID, storage indexes will be used. To further test this, let’s run the below:
From this we can see that the volume of rows being returned does play a role in 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