Storage Indexes - Oracle Exadata Recipes: A Problem-Solution Approach - page 544

Figure 19-1. Storage indexes in action
In Figure 19-1 , we are showing the storage regions that comprise a single ASM allocation unit for the table used
in the solution of this recipe. The first storage region contains a minimum value for OBJECT_ID of 1 and a maximum of
99, the second has a minimum of 100 and maximum of 199, the third has values between 200 and 299, and the fourth
has values between 300 and 399. When I/Os for this table are issued using the predicate condition supplied in the SQL
statement above, Exadata will examine the region indexes for each storage region and bypass physical I/O requests
to storage regions 1 and 4. Region 3 has a minimum value of 200, so based on the between predicate, cellsrv will
perform a physical I/O to this region.
It is worth mentioning that Exadata automatically maintains storage indexes over time based on your application
usage. There is nothing an Exadata DMA can do to influence storage index behavior outside of potentially ordering
your data to encourage their use or change your application's query predicates. Recipe 19-2 discusses the impact of
ordered data with respect to storage indexes.
19-2. Measuring Storage Index Performance with
Not-So-Well-Ordered Data
Problem
You wish to measure and contrast the storage index performance savings with well-ordered and randomly
ordered data.
Solution
In this recipe, you will learn how to measure and contrast storage index I/O savings for queries that retrieve data from
tables with well-ordered data and tables with randomly ordered data. In the following test case, we will use the table
introduced in Recipe 19-1 and create two identical tables, ordering our data on the OBJECT_NAME column for one table