Exadata Storage Indexes – Part I (Beginning To See The Light) October 4, 2012

Thought I might discuss Exadata Storage Indexes, explore what they are, how they work and discuss their advantages and disadvantages. Following is but a brief introduction on this very cool Exadata feature.

A Storage Index basically maintains summary information about database table data down on the Exadata storage servers. The information consists of the minimum value, the maximum value and a flag to denote whether any Nulls exist for up to 8 different columns within each 1M storage region of disk.

My little diagram above attempts to describe this (click on image for a larger version).

The Storage Indexes are created automatically and transparently based on the SQL predicate information executed by Oracle and passed down to the storage servers from the database servers. Storage Indexes take up no physical storage of themselves and are built and maintained entirely in memory. As only this very basic summary information is stored for a maximum of 8 columns for each 1M of storage, Storage Indexes are very lightweight and can be created and maintained with minimal general overheads.

So how are they used ?

During an Exadata Smart Scan, Oracle can perform predicate filtering down at the storage layer and so only return just the rows of interest back up to the database. As part of this process, Oracle can use the Storage Indexes to visit just the 1M storage regions that can potentially contain rows of interest. Those storage regions that can’t possibly contain data of interest can be eliminated and not accessed at all during the Smart Scan operation.

So, in a very simple example, if we have an SQL predicate such as WHERE CODE = 5, if a corresponding Storage Index on the CODE column of the first 1M region of the table has MIN=2 and MAX=10, Oracle would need to access this portion of the table as the CODE value of interest could potentially exist here. However, if the next 1M storage region had a CODE Storage Index with a MIN=7 and MAX=12, then the CODE value of 5 can’t possibly exist within this portion of the table and can be ignored and not accessed at all during the Smart Scan.

So depending on the column, predicates and data distribution, a Storage Index can potentially eliminate having to physically access significant portions of a table during a Smart Scan. In an extreme example, on a search of a CODE value = 42 where the maximum CODE value that actually exists is say 35, a Smart Scan can perform a so-called Full Table Scan (FTS) via a Storage Index that doesn’t actually have to perform any physical I/O at all and can ignore the entire table.

The less physical I/O performed, the less work required and the faster the response time. If a FTS of say a 500GB table only had to physically read and access a few MBs here and there AND just return the data of interest back to the database servers, that would significantly improve the overall performance and overheads associated with the FTS.

Why read 500G when you can get the necessary data by reading just a few MB. Exactly what indexes are designed to do🙂

Storage Indexes can be used in combination with each other such that a storage region can be avoided if an column 1 can’t possibly be there or column 2 can’t possibly be there as in the case with AND based predicates where both columns values must be true.

Incase the SI has flag ‘Y’ for a given column and range , in all the scattered range(s) then it might end up reading all data. Basically not possible to filter out data which has not null value when only looking for NULLS !

Why not to keep NULLS separate.

Index with Null is always messy !!

This will work best with Number & Date Datatype.

With Varchar2 Datatype say on column first_name, how to define range ?

Hello Richard, than you very much for the insights. I have some questions. You said ” Storage Indexes take up no physical storage of themselves and are built and maintained entirely in memory. ” That means after each startup of the instance, the SI (storage Index) need to be re-calculated? And it is a data distribution pattern, it must read the whole database again and again just after each instance re-start? In which memory area SI is stored? What is the approximate size of the SI for 1MB data?

Storage Indexes are created in the memory of the storage servers, not the database SGA. Therefore, it’s not the startup of the database instance but of the storage server that would require the SIs associated with the storage server to be re-created. So in may well be for a specific table that some portions of the SI may not be currently created (this can occur for a number of reasons). Note though that as there’s no physical object to create, re-creating a SI is nowhere as costly as a corresponding physical database index.

The approx size of a SI for a 1MB storage region depends on the size of the indexed column but it’s relatively tiny as it just needs to basically store 2 values (the min and max values of the column) and a null flag. Compare this to a database index which basically needs to store all the indexed values within a 1MB of table data, plus all their corresponding rowids plus overheads.

My next post will be on the differences between SI and Database indexes and will cover some of these topics.

Yes, the clustering of data can be an important determination on the usefulness of a storage index. I’ll cover this later.

The null flag makes it possible for Oracle to ingore whole regions of data if nulls are not particularly common. It’s usefulness of course will depend ..

Varchar2 columns work in exactly the same way, the min/max is stored and compared. If the ranges are wide and ellimination not particularly effective, then it might not end up being created and being one of the “lucky 8”.

[…] reason for this improvement is due in large part to the use of Exadata Storage Indexes. As discussed previously, a Storage Index can potentially automatically avoid having to read significant portions of data […]

I had a few queries
1. How can we view information on the storage indexes that exist at a point in time.
2. How frequently are the storage indexes update. IE Do they get updated fro every insert and update.
3. Is there any performance overhead on DML activities when we are using storage indexes.