Designing for Exadata: Maximizing Storage Indexes Use

Posted in: Technical Track

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.

email

Interested in working with Christo? Schedule a tech call.

About the Author

An Oracle ACE with a deep understanding of databases, application memory, and input/output interactions, Christo is an expert at optimizing the performance of the most complex infrastructures. Methodical and efficiency-oriented, he equates the role of an ATCG Principal Consultant in many ways to that of a data analyst: both require a rigorous sifting-through of information to identify solutions to often large and complex problems. A dynamic speaker, Christo has delivered presentations at the IOUG, the UKOUG, the Rocky Mountain Oracle Users Group, Oracle Open World, and other industry conferences.

24 Comments. Leave new

Log Buffer #199, A Carnival of the Vanities for DBAs | The Pythian Blog
August 13, 2010 4:22 pm

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

Reply

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

Reply
Christo Kutrovsky
August 14, 2010 5:31 pm

I totally agree Greg. And that’s the advantage of a software solution. Upgrades are coming.

But until then, we have to deal with what we have.

Reply
Rick Greenwald
October 5, 2010 7:50 am

If forever wasn’t such a long time . . .

Reply
Alex Gorbachev
August 14, 2010 4:10 pm

“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.

Reply
Christo Kutrovsky
August 14, 2010 5:33 pm

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.

Reply
Connor McDonald
August 22, 2010 11:17 pm

“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 :-)

Reply
Christo Kutrovsky
August 23, 2010 9:57 am

Connor,

Agreed. But in some cases, certain dimensions, you don’t want to “update” your data, you want to leave it different.

You have to choose the right mix/balance.

Reply
Alex Gorbachev
August 24, 2010 6:21 pm

That would be SCD Type 2.

Reply
Alex Gorbachev
August 24, 2010 6:21 pm

if I want to change “Human Resources” to “HR”

Well, it would depend whether it’s a slowly changing dimension type 1 or 2. Eh?

Reply
Oracle Exadata HCC (Hybrid Columnar Compression) removes the 255 columns limit | The Pythian Blog
August 25, 2010 5:02 pm

[…] 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 […]

Reply

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!

Reply
Christo Kutrovsky
September 10, 2010 3:33 pm

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.

Reply

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.

Reply
Christo Kutrovsky
September 13, 2010 5:03 pm

Stuart,

Please see the updated post and the new blog entry I linked that made me realize a mistake.

Thanks for inquiring on this, without your question, this error would not have been found.

Reply

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?

Reply
Christo Kutrovsky
September 10, 2010 4:20 pm

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.

Reply

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

Reply
Christo Kutrovsky
September 13, 2010 5:01 pm

Kerry,

Thanks for pinging me back on this and doing the extra research. I updated the blog with some additional information.

Reply
Correction on Storage Index blog | The Pythian Blog
September 13, 2010 5:08 pm

[…] 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 […]

Reply

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?

Reply

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

Reply
Christo Kutrovsky
January 31, 2011 6:53 pm

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

Reply
Exadata « Oracle Scratchpad
July 11, 2012 3:23 am

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

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *