Databases Reference
In-Depth Information
Chapter 19
Storage Indexes
Storage indexes are a feature unique to Exadata. A storage index is a memory-based structure that reduces the amount
of physical I/O required by the cell when accessing data via Exadata Smart Scan. A storage index tracks the minimum
and maximum values retrieved based on your application's
WHERE
clause predicates and builds storage indexes based
on usage.
In contrast to traditional B*Tree indexes, which tell Oracle exactly where to find a segment's extents, storage
indexes behave like “anti-indexes”; their purpose is to tell Oracle with absolute certainty that a requested extent
does not
exist in specific locations, and Exadata uses this information to bypass I/O requests to the physical storage
locations.
By default, storage indexes are created and maintained automatically by Exadata's cell services software. As
an Exadata DMA, you can influence storage index behavior using a number of methods, including forcing direct
path reads with full table scans, disabling storage indexes using the
_kcfis_storageidx_enabled
undocumented
initialization parameter, or reordering the data in your tables to make storage indexes more beneficial.
In this chapter, we'll talk about conditions required for storage indexes to be used, present cases for different
types of queries and storage indexes, and show various tracing and diagnosis methods.
19-1. Measuring Performance Impact of Storage Indexes
Problem
You wish to measure the performance impact when Exadata Storage Indexes are used.
Solution
In this recipe, you will learn how to measure the I/O savings provided by storage indexes using the
cell physical IO bytes saved by storage index
statistic.
Begin by issuing a SQL statement that qualifies for Exadata Smart Scan and run the script in Listing 19-1 to
display the I/O savings as a result of storage indexes:
SQL> select count(*) from d14.myobj_uncomp
2 where object_id between 100 and 200;
COUNT(*)
----------
101000
Elapsed: 00:00:00.16
Search WWH ::
Custom Search