A consultant recently suggested changing the FACT table of our data warehouse to be index organised. I couldn't see a lot of advantages of this, except perhaps in space saving. I was wondering if anyone has tried it and got any benefits.
Uh, can't say as that makes any sense to me either.
Here is a link where we talk about indexing strategies for fact tables. Basically - lots of bitmap indexes.
It would be very rare for you to actually access facts via the PK, which should generally be your only B*Tree, and, hance, organize-able index. So, if you don't access it via that index, why organize via that index?
There are many benefits having a fact table defined as IOT. They are:
1 The space used for PK is not required. Hence, multiple I/Os on Index and data are not required.
2 Since most of the fact table has date (or time key) as the first column in the table as well as first column in the index, that is always used in the WHERE clause, speed of retrieving the data is maximized.
3 IOTs can be partitioned
1 Physical rowid is NOT maintained on IOT. You are left with only logical ROWID. In case of block corruption, and you want to extract maximum number of rows from the table, you have to use PK only not the min or max of rowid.
2 PK constraint can not be dropped
3 Unique constraints are not allowed
4 Distribution and replication are not allowed
5 Bit Mapped Index is not allowed on IOT
Carefully evaluate the requirements, particularly ad hoc queries. If the table requires bit mapped indexes on columns such as Male/Female, Status (Active/Inactive), YES/NO etc, then IOT is NOT a good choice.