Re: DBA - clustered tables - WHY ? - WHO USES THIS ?

In <4toesm$2ai8_at_news-s01.ny.us.ibm.net>, roosens writes:
>Could any point out the advantage- when you should use or more important when NOT to
>use them.
>How can you estimate the profit ?
>How can you measure your profit after applying them ?

Hmmm, ORACLE probably will kill me for saying this, but I only had success in using clusters in a fashion totally outside of what they recommend. Instead of using them to save join I/O, like ORACLE suggests, I used them to save space and cluster rows of the same key value (duplicated, of course) on the same ORACLE block. I.e, instead of clustering two tables, I just clustered a single table with a long key and plenty of duplicate values in that key. Ended up with heaps less I/O when retrieving all rows of a certain value of the key. Same result however could be obtained by sorting rows by key before loading into a standard non-clustered table, but I didn't have an easy to use sort utility at the time, so I used clustering. Also, clustering only stores the key once, whereas the table stores it all the time.

Apart from that, I've never been able to realise the "magic" improvements that ORACLE claims for clustering. And, if you ever feel inclined to use it, never NEVER use them with a 2k block size: absolute minimum is 4k, better 8k.

Nuno Souto
Database Consultancy Services P/L
Email: nsouto_at_acay.com.au
Isn't life wonderful? Received on Wed Aug 07 1996 - 00:00:00 CEST