Storage Indexes, OLTP With Well Ordered and Not-So-Well-Ordered Tables

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 OLTP Tables

In previous posts, we’ve seen how storage indexes can benefit queries against tables with predicates searching well-ordered tables. In this post we’ll explore test cases against tables that are “OLTP by nature” – meaning, tables with sequence-generated values in key columns.

Let’s simulate a real-life OLTP table, without indexes, in which a sequence may be used to populate a column incrementally. The difference between this test and a real OLTP workload is that in our storage index testing, we won’t have any “real” indexes.

In our first test, we’ll do an equality join:

We see the expected storage index scan. Let’s see if we can range-scan this and get it to use a storage index:

So far so good, storage indexes in play and making a big difference. Now let’s see how wide we can make our range scan and still get the benefit of a storage index:

The query response time was under a second by nature of using storage indexes even after a million rows. Try getting that performance with a 1,000,000-row standard index range scan! But will this success hold up when selecting more than just one column?

The answer appears to be an emphatic YES. Let’s try to find the breaking point and change the range scan up to 15 million rows, or almost half the size of the table:

As we can see above, Oracle used a storage index even when it needed to access nearly all the rows in the table. This is due to the fact that it’s perfectly ordered on the predicate column. So can we do anything to make it actually avoid using a storage index? The answer it yes – put an upper bound limit outside the range of the maximum value in the column:

The examples in this section should really get you thinking of the potential power and benefit of storage indexes in an OLTP system. If your system does unique index scanning on sequentially-generated number or even if it does wide scan ranges, the performance savings of storage indexes can drastically outpace normal B*tree index access methods. It all boils down to your column ordering. If you consider this from the standpoint of being able to drop your large indexes on OLTP tables, it’s quite an interesting consideration.

Before closing out this section, let’s bring it back to a more real-life scenario in which OLTP tables are joined with other tables, some of which are well-ordered, some ordered on different columns, some not ordered in any discernable fashion at all. In the tests below we’ll join our three MYOBJ% tables and see what we can learn:

In the second example, we saw storage indexes in use, likely due to the optimizer leading with the MYOBJ_ORDER table, so this in part contradicts Oracle’s documentation that storage indexes are not viable for joins. The referenced documentation could be outdated so I won’t necessarily blame Oracle documentation. The first test showed no storage index in use, but Smart Scan processing did scan huge amount of un-indexed rows relatively quickly. If we do other tests with a larger result set joining all three tables, we see that not only are storage indexes not used, but also the offload efficiency drops. If, however, we remove MYOBJ (the un-ordered table) from the query and do a 2-way join, storage indexes will be used and performance will be very good.

Summary

• OLTP tables with sequentially-populated columns (i.e., ORDER_ID, TRANSACTION_ID, etc.) could be great candidates for storage indexes
• Storage indexes will not be used when the upper bound of a range scan is outside the bounds of what values exist in the table.
• Storage indexes can be used for joins but your mileage will vary based on the query predicates and how well ordered every table is in the query.