Index organized tables in data warehouses

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.