Designing for Exadata: Maximizing Storage Indexes Use

Aug 13, 2010 / By Christo Kutrovsky

Tags: , , ,

Oracle Exadata V2 is a very well balanced database machine combined with smart and innovative software. One of these innovative features is the Storage Index (see also Marc’s post here) – a game changing feature in my opinion.

What is the Storage Index?

The Storage Index is more similar to partitions than to an index. It should be called an anti-index. It allows Oracle to skip reading granules of 1Mb when they are known not to have the data requested. Similar to bloom filters (See Christian Antognini ‘s paper) they reduce the amount of work required, similar to partition elimination, but far more granular, and fully automatic on ALL columns (certain datatypes excluded).

Here’s an extreme example of a Storage Index at play. CKTEST is a 80 Gb table, with no partitioning nor any kind of indexes. The ID column is the equivalent of a primary key, and ever-incrementing value.

select * from cktest where id = 283942;

cell physical IO bytes eligible for predicate offload	81 920 000 000
cell physical IO bytes saved by storage index	81 917 984 768
physical read bytes	81 920 212 992
physical read flash cache hits	0
physical reads	10 000 026
physical reads cache	26
physical reads cache prefetch	0
physical reads direct	10 000 000


This query scanned an 80 GB table with no indexes and returned in 0.3 seconds.

Notice how Oracle actually reports physical reads, when physical reads did not happen. Oracle “fills up” the saved reads with the granules that it would have read if storage indexes were not in place.

Note that this feature is no replacement for primary key lookups. A cached primary key lookup is somewhere in the 0.000003 seconds range.

How do design for and leverage Storage Indexes?

The most important component is data locality. Similar data (for example same region) needs to be physically together in order to fully benefit from storage indexes. Some data naturally sits together, such as most ever-incrementing numbers (most ids) as well as dates.
For example, during ETL processes, batches of data should be ordered when loaded into the table. By product, or by region, or any combination that will be effective. For example, you no longer need to index your “batch_id” equivalent column.
This is not that different from classical non-Exadata datawarehouse design with bitmap indexes. Data locality helps to keep bitmap indexes extremely small, as well as queries using them can benefit multi-block reads from index range scans. Oracle would scan one index row, and then reads up to 128 table blocks in a single request, if those blocks are sequential.

It sounds simple, and it is. Just order the data, and leverage an amazing optimization.

Ordering similar data together also allows for better compression. Especially with the new HCC (Hybrid Columnar Compression), much higher ratios can be achieved. And all the decompression is offloaded to the storage cells, so in theory, there’s no overhead.

Limitations

As with every great feature, there are limitations on when they can be used.

Updated: 13 Sept 2010

Storage indexes are used during smart scans. All the limitations to smart scans apply to storage indexes. They do not work with joins. Bind variables are supported, however it’s slightly more restrictive than regular indexes/queries. For example the query:

select * from table where id = :x;

Will only use storage indexes if :x matches the datatype of the table. Contrary to regular indexes, where if “id” is number and “:x” is string, the implicit convertion of to_number(:x) will still use a b-tree index.

When I originally posted this blog, I reported as storage indexes not working with bind variables, as I had only experimented with number columns and varchar binds.

Note however that in literals, a datatype convertion of “number_column = ‘4’ ” (the string ‘4’) does not disable storage indexes.
However “varchar_column = 4″ (the number 4) disables them. So in a way, the disable of storage index usue due to implicit convertion works in the exact opposite way (with a twist for binds) than with b-tree indexe use, where number = string uses the index, but string = number does not. Oracle converts the later to “to_number(column) = number” which disables the index.

I am sure that one will bite a number of people in the future, especially ones with past implicit convertion experience.

Credit for this update goes to Kerry Osborne’s blog post, who did some additional research. As a result, this update comes from me.

End Update from 13 Sept 2010

This raises the question of whether STAR schema should be at all used with Exadata.

What’s the purpose of dimensions in large fact tables? Compression! They are used so that highly-repeated data is “extracted” in a common area, and a pointer to it (foreign key) used instead. There are other uses of course, particularly when determining what kind of search criteria we have.

But combined with HC compression, the benefits of dimensions becomes questionable. Storage indexes and pure-flat tables, very similar to text files might be better.

Of course, any combination of the two is quite a valid approach. This is why Oracle is so great – all the features come in the same package. Which one you use and how you combine them is what defines how well a database will perform.

Other limitations

Since storage indexes are anti-indexes, i.e. they tell you where you data is not, they can’t be used in all manners real indexes and partitions can. For example min() and max() cannot currently benefit from storage indexes, although in theory they could.

Also, since storage indexes are part of smart scan, they cannot be used in any combination (for the same table) with other indexes. You either perform a full table scan and use storage indexes, or use classical multiple bitmap indexes.
They do however work well with partitions and sub-partitions. And partition elimination works with bind variables, with subqueries, and in even more ways than you may be used via the JOIN-FILTER execution path (a variation of bloom-filtering).

At this point you have a design choice:which queries would be a bitmap index/dimension combination, which ones will be straight literals with storage indexes.

Caveats

The optimizer has no clue of storage indexes. What this means is that a query’s execution path will be calculated as if storage indexes did not exists. This means anyone designing a mix in the same table of classical index design with flat storage index design may have to use hinting to ensure queries intended to use storage indexes run with full table scans. Of course this is only for queries that have both predicates on the same table that could use bitmaps.

And a final caveat, DML (UPDATE, DELETE probably MERGE as well) cannot use storage indexes elimination in serial execution. And parallel DML locks the table (or partition) preventing any other modifications of the table (reads are still fine) for the life of the transaction.

Conclusion

As with any feature, there are intended use cases, limitations and caveats. Use it right, and amazing performance gains can be achieved. Use it wrong, and nothing will happen. This is what is great about storage indexes in particular. They are there, without any overhead. It’s only a question of how to leverage them, in addition or combination withevery other feature that Oracle Exadata has to offer.

24 Responses to “Designing for Exadata: Maximizing Storage Indexes Use”

  • […] And to close things off a last minute entry by Christo Kutrovsky – designing for Exadata, maximizing storage indexes use. […]

  • Greg Rahn says:

    Today Storage Indexes do not work with joins, but doesn’t mean it will be that way forever… :)

  • “fully automatic on ALL columns”

    I understand that SI can be built on ANY column (providing datatype limitations are met) but Oracle doesn’t build SI on al columns automatically. Exadata storage cells build SIs only on columns that are frequently used in filtering criteria.

    • Alex, there’s little information on the subject.

      But it does build on all columns. Even when you scan columnA, then later if you run a query on columnB, if you can, you will benefit from storage indexes. They are build on the fly for all read data.

      Even writing creates the storage indexes. However, for whatever reason, when I experimented with this, only half the data was indexed. After my first query, all data was indexed.

      But yes, they are indeed in the storage cells, not compute nodes.

  • Connor McDonald says:

    “What’s the purpose of dimensions in large fact tables? Compression!”

    I dunno if that’s the primary reason for them … if I want to change “Human Resources” to “HR”, I think I’d rather update a dimension table instead of my 5 trillion facts :-)

  • […] a follow up from my previous post on Exadata Design, where I question the use of dimensions for certain attributes in data warehouses, I figured I […]

  • Stuart Wong says:

    Are the limitations you have mentioned due to your own investigations or is there an Oracle document reference? Specifically concerning not being useful for bind variables, and so on? I would greatly appreciate this to review with my colleagues as this is the only place I’ve found such information. Thanks, for the useful information as always!

  • Stuart,

    I haven’t specifically looked for where in the documentation it says it, but that’s how it works.

    Storage index “elimination” is part of smart scan. And smart scan only works (today) with literals.

    Note that column projections (i.e. sending only columns that are been selected) is not affected by this.

    • Stuart Wong says:

      Thanks for the quick reply Christo. Since this the only place I’ve seen “..smart scan only works (today) with literals..” mentioned I’d love to have a document reference of some sort to show to my peers. I can’t find any mention of this on MOS, or other internet sites to date.

      Thanks again.

  • John Black says:

    Great article! I’ve scoured the internet and this is the only mention of this limitation I’ve seen. The marketing for Exadata implies when you migrate that you can drop all your indexes and always use full table scans because of smart scan…but if you’re using Exadata as an OLTP (or at least mixed load) on a busy database, and you’re not using bind variables, you’d quickly develop issues with your buffer pool, correct?

    Customers looking at purchasing Exadata need to estimate the size of a database once its migrated to Exadata, since Oracle licenses cells by spindle.
    Assuming you do need indexes for OLTP on Exadata, in practice, what ratio of index to table storage do you commonly see? Obviously this depends on access patterns and database design, but on average, what do you see is common? Indexes are very rarely created, or the design is typical to any other 11.2 database?

    • John,

      Do not forget, smart scans (which include storage indexes, column projections and etc) are intended for Data Warehouses.

      Datawarehouse queries in general should not incline as much to bind variable use, it’s not really so critical.

      Marketing is mostly right, you have so much bandwith combined with smart scan you can drop all your indexes.
      Whether that’s the right design for what you are trying to accomplish is entirely different story.

      If you are using Exadata as OLTP, the cell flash cache will be your best friend.

      As to the ratio for data vs index, there is no general rule. It is what it needs to be.

  • Christo,

    Thanks for the post. Lot’s of good information on a topic that is not very well understood yet. I did want to point out though that I think Smart Scans and in fact Storage Indexes can be used with bind variables. I have just posted some examples on my blog. I’d be interested in your thoughts.

    Kerry

  • […] is just a quick post to note that I’ve corrected my blog on Storage Indexes here, after a follow up blog from Kerry Osborne indicating an error on my […]

  • Amir Riaz says:

    Nice post, However the things which surprised me most is:

    For example min() and max() cannot currently benefit from storage indexes, although in theory they could.

    did you tried your query with serial full scan or parallel full scan?

  • Uwe Hesse says:

    Hi Christo,
    thank you for the informative posting! One thing that I cannot confirm, though, is the alledged incapability of storage indexes to work together with bind variables. This is no limitation – no matter whether implicit datatype conversion takes place or not.
    Look at this little example:

    SQL> connect adam/adam
    Connected.
    SQL> select name,value from v$statname natural join v$mystat where name like ‘%storage%';

    NAME VALUE
    —————————————————————- ———-
    cell physical IO bytes saved by storage index 0

    SQL> select * from sales where channel_id=99;

    PRODUCT CHANNEL_ID CUST_ID AMOUNT_SOLD TIME_ID
    ————————- ———- ———- ———– ———
    Oracle Enterprise Edition 99 960 5000 01-SEP-10

    SQL> select name,value from v$statname natural join v$mystat where name like ‘%storage%';

    NAME VALUE
    —————————————————————- ———-
    cell physical IO bytes saved by storage index 4418281472

    SQL> vari n number
    SQL> exec :n:=99

    PL/SQL procedure successfully completed.

    SQL> select * from sales where channel_id=:n;

    PRODUCT CHANNEL_ID CUST_ID AMOUNT_SOLD TIME_ID
    ————————- ———- ———- ———– ———
    Oracle Enterprise Edition 99 960 5000 01-SEP-10

    SQL> select name,value from v$statname natural join v$mystat where name like ‘%storage%';

    NAME VALUE
    —————————————————————- ———-
    cell physical IO bytes saved by storage index 8836562944

    SQL> vari c char(2)
    SQL> exec :c:=’99’

    PL/SQL procedure successfully completed.

    SQL> select * from sales where channel_id=:c;

    PRODUCT CHANNEL_ID CUST_ID AMOUNT_SOLD TIME_ID
    ————————- ———- ———- ———– ———
    Oracle Enterprise Edition 99 960 5000 01-SEP-10

    SQL> select name,value from v$statname natural join v$mystat where name like ‘%storage%';

    NAME VALUE
    —————————————————————- ———-
    cell physical IO bytes saved by storage index 1.3255E+10

    • Uwe: I have corrected this inaccuracy in the blog long time ago. Are you not seeing an updated version by any chance?

      Extract:
      Storage indexes are used during smart scans. All the limitations to smart scans apply to storage indexes. They do not work with joins. Bind variables are supported, however it’s slightly more restrictive than regular indexes/queries. For example the query
      ***end extract***

      The combination that is not working in OUR version is:

      num_col = :varchar_bind

      SQL> variable id varchar2;
      SQL> variable id2 number;
      SQL> exec :id2 := 1;

      PL/SQL procedure successfully completed.

      Elapsed: 00:00:00.00
      SQL> exec :id := ‘1’;

      PL/SQL procedure successfully completed.

      SQL> set timing on
      SQL> select /*+PARALLEL(t,2)*/ count(*) from ckutrovsky.ckbig t where id = :id;

      COUNT(*)
      ———-
      2

      Elapsed: 00:00:10.37
      SQL> select name,value from v$statname natural join v$mystat where name like’%storage%';

      NAME VALUE
      —————————————————————- ———-
      cell physical IO bytes saved by storage index 0

      Elapsed: 00:00:00.00
      SQL> select /*+PARALLEL(t,2)*/ count(*) from ckutrovsky.ckbig t where id = :id2;

      COUNT(*)
      ———-
      2

      Elapsed: 00:00:00.68
      SQL> select name,value from v$statname natural join v$mystat where name like’%storage%';

      NAME VALUE
      —————————————————————- ———-
      cell physical IO bytes saved by storage index 8.1821E+10

      Elapsed: 00:00:00.00
      SQL> select /*+PARALLEL(t,2)*/ count(*) from ckutrovsky.ckbig t where id = :id;

      COUNT(*)
      ———-
      2

      Elapsed: 00:00:10.30
      SQL> select name,value from v$statname natural join v$mystat where name like’%storage%';

      NAME VALUE
      —————————————————————- ———-
      cell physical IO bytes saved by storage index 8.1821E+10

      Elapsed: 00:00:00.01

  • […] Making the most of Storage indexes […]

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>