Re: Large table and buffer cache concerns

>
> Would you have an idea of the pro and con of
> one over the other, in the context of the OP
> requirements ?
>

Raoul, I know your question is specifically directed at Mr. Lewis, but I'd like to take a stab at a few of the differences. Perhaps Jonathan could clarify any of my oversights.

1.) Both reduce disk I/O as the data is co-located, the IOTs go one step further by eliminating one I/O, namely, the read of the table. 2.) With IOTs one can rebuild them online and the secondary indexes still work, sort of. They can become stale, perform slower as the guess component of logical rowid becomes invalid. But they are still there, alive and kicking.
3.) IOTs offer additional storage savings through the use of compression, but at a cost of additional CPU to rebuild the key. 4.) IOTs reduce data storage by combining the PK index and data segments within the same blocks.

So when it comes to using a IOT vs a single table b*tree cluster there are still some situations where one would want to use a cluster.

1.) Generally, you get the best bang for your buck with the use of an IOT when not using the overflow functionality. So in cases where a record would need to overflow, a cluster might be a better choice. Note I said might be, I've no benchmarks to back that up. 2.) Both use a b*tree, so both options are susceptible to hot blocks. A prime example, the PK is populated via a sequence, so the "right edge" of the index is always splitting. Within an IOT each block may contain fewer keys than the index of the Cluster so one may experience less contention for a single block. This is primarily a concern for RAC implementations, which can be remedied through proper partitioning. (This next bit I am unsure of, still researching) To address "right edge" splitting without partitioning, reverse key indexes might be an option for a cluster table's PK but not an option for an IOT.

One other area I'd like additional clarity upon would be if there is any additional overhead associated with splitting an IOT leaf block vs splitting the leaf block of the clusters PK. One would assume fewer splits with the cluster index as it is more densly packed than the IOT's as the IOT also contains the data.

> Devalos, if you happen to benchmark both,
> would it be possible to post your results
> here ? That would be much appreciated.

Can do. Received on Sun Apr 15 2007 - 09:54:53 CDT