Re: bitmapped secondary indexes on an IOT partitioned file.

>
>

Thank you. I agree, there's no such thing as a free lunch. IOTs do have their trade offs. I'm hoping to arrange the primary keys such that most queries will use them or a prefix of them. However, the only requirement that I have is "instant" response time.

We will have more than 1 TB of data and fact tables with over 30 M rows. I expect that most queries will return a tiny percentage of the rows (but it may still be a large number).

Right now I don't have the resources to run those very careful tests. However, I certainly will be monitoring the warehouse closely after it is in production. If it turns out that IOTs are a bad idea I should have ample opportunity to restructure the fact tables and I should also have a better idea how to subpartition to produce a fine-grained structure that matches the users' needs. Right now my heap subpartitioning on the dimension tables is just based on the idea of getting as many spindles involved as possilbe.

If I get a RAID 0+1 storage and SAME it then the heap subpartitioning becomes a moot point and I can try to find a better way to get that fine-grained partitioning. Right now no one is asking for my advice on storage; nor are they likely to take it if I offer it unsolicited. The physical side of the data warehouse is under a different contract with a different contractor. Given that they'll want to do it as cheaply as possible, I expect to end up with RAID 5.

--
http://www.freelists.org/webpage/oracle-l