When is Exadata’s storage indexes used?

When is Exadata’s storage indexes used?

Exadata’s Storage indexes provides data pruning at the storage layer.  The storage indexes eliminating disk IO, by storing a summary of the data distribution on the disks.    MOS Note ID 1094934.1 provides the following summary:

Each disk in the Exadata storage cell is divided into equal sized pieces called storage regions (default 1MB). There is an index entry for every storage regions (1MB of data stored on disk). Each entry contains the minimum and maximum value for columns seen in ‘where’ clause predicates. Information for up to 8 columns can be stored. The index is then used to eliminate disk IO by identifying which storage regions don’t match the ‘where’ clause of a query.

The question I’ll try help answer in this blog post is, when Oracle can take advantage of the storage indexes, by sharing some of the test results and findings I came across when testing storage indexes.  The data used for these tests are simple versions of a dba_tab_columns including an id column.   The tests were performed on a quarter rack ExaData database machine (2 db nodes – with 16 cores each and 3 storage servers)

 SQL> select TABLE_NAME, NUM_ROWS, SAMPLE_SIZE, LAST_ANALYZED from user_tables where table_name like '%PBA%'; 

Check the storage session statistics before testing

Check the storage session statistics before testing

SQL> select avg(pba_id) from pba_order where pba_id between 1000000 and 1000999;   - Range scan on sorted data.

Check the storage session statistics before testing

SQL> select avg(pba_id) from pba_order where pba_id <100;      -- LESS than on sorted data

So we can confirm that Oracle can use storage indexing for both less and greater than predicates.

Check the storage session statistics before testing

Before finishing up, let’s check if storage indexes are kicking in on tables compressed with EHCC.   

Check the storage session statistics before testing 

SQL>  select avg(pba_id) from pba_order where pba_id <100;

I you like to read more on EHCC; I have blog post on EHCC with a few test-case and conclusions on http://www.oaktable.net/blog/what-can-ehcc-do-you

Conclusion

In Exadata, the Storage Index can evaluate predicates like <, <=, =, !=, >=, >, is NULL, is NOT NULL, as you saw above I was not able to get the storage indexes to kick in for the between and equal predicate for the random sorted data.    It may work in other circumstances or test-cases; currently it is very hard to evaluate, as there is no public tracing tool available. 

In general we saw marginal better IO savings on the test-case with sorted data and that storage indexes also worked on EHCC compressed tables.    I have also performed the same tests on a global temp table and the storage indexes did work with global temp tables as well.

Kerry Osborne has a good blog post, confirming that Storage indexes also works with bind variables: http://kerryosborne.oracle-guy.com/2010/09/do-storage-indexes-work-with-bind-variables/

The performance gained from the storage indexes really rocks my world and it made me review our design and index strategy for our warehouse.