Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Oracle Cluster Index Performance

Re: Oracle Cluster Index Performance

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 10 Jul 2001 14:23:13 -0700
Message-ID: <9ifro1031h8@drn.newsguy.com>

In article <1a25f735.0107101307.59893837_at_posting.google.com>, echang_at_surffast.com says...
>
>I was wondering if anyone can tell me about their real world
>experiences with cluster indexes. I have recently experimented with
>these and have not seen increased performance gains from this.
>
>The cluster key is simply a unique number generated from an Oracle
>sequence. There are about 100 rows/cluster key and each row is 300
>bytes. The size I used for the cluster index is 30000. My Oracle
>block size is 8K so I know that there is some row chaining going on.
>
>Is there anyone out there that has experience with this sort of set
>up?
>
>Eric Chang

how are you testing it?

how do you use the data?

how are you measuring performance (single user or real world with lots of concurrent access)?

The advantage of the btree cluster will be less blocks needing to be cached overall. When you retrieve the block -- you retrieve many|most|all of the values around that cluster key. If your application would have had to of retrieved all of those 100 rows using an index range scan into a "disorganized" table -- we may well have had to of cached 100+ blocks (worst case). With the cluster -- hopefully we cached 1+ blocks.

In a single user test, where you own the SGA, you might not notice that it took 100+ blocks to get your result versus 1+ blocks. In a live system with tons of users -- it may become very evident that your buffer cache is infinitely more effective.

You use cluster objects every day -- various parts of the data dictionary are stored in clusters.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 
Received on Tue Jul 10 2001 - 16:23:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US