Index organized tables in data warehouses
January 10th, 2007 by Peter Scott
Like a lot of newfangled stuff, IOTs are perhaps less commonly used in DW systems than they might be. In part this is an extension of the “I’ve always done it like this, so why change” attitude, and in part it identifying a suitable use where the performance benefits can shine. IOTs are not silver bullets (or even silver needles as D Aldridge posted earlier today) Used wrongly they are a detriment; and as we all believe, one bad experience can taint an opinion for life.
Recently I wrote about tuning a query for fraud detection for one of our customers. The original query took around 15 minutes to return the result. It had to take a 90-day subset of a 600 million row table (about 20% of the rows) and join it to another large table on an unindexed pair of columns. For various reasons around the partitioning scheme in use and the needs of the dataload process it was not possible to create suitable global indexes on the two tables. So we materialized the join into a lightweight index organized table that just held the query keys and the fact we needed to detect and only populated for the date range of interest. Further we partitioned the IOT to allow simple maintenance of the rolling 90 day window. Result query time is now sub-second, and therefore usable in realtime.
So in effect we used the IOT as a lightweight facade for a complex join that (for one reason or another) we did not want to index. Apart from the measure to report, the only columns in the IOT were the ones needed to ensure uniqueness. And being partitioned by day, purging of old data is very straightforward.
January 11th, 2007 at 1:15 am
This is a good use case indeed.
I used it to the same benefit – partitioned IOT combines features of clustering and partitioning. In one case it was a de-normalized join of 4 tables and instead of tens of seconds resource intensive query delivered sub-second performance with just few block reads returning several hundred rows. There were some negative effects as that was OLTP system and maintenance of partitioned IOT is a headache – can’t do partition move online (at least in 9i).