RE: Need Cluster Help
Date: 1996/06/10
Message-ID: <01bb5680.2b302a00$0305eecd_at_katkins.iccom.com>#1/1
Jim,
According to my understanding of clusters, it would definatly NOT help in your situation. Clusters are designed to be used when rows from multiple tables are usually queried together. Clusters achieve their performance gains by placing all of the rows that are to be queried together in the same physical data block. Therefore in a standard parent-->child table relationship, all of the children records in the child table will be placed in the same block as thier parent records. The cluster key is usually the foreign key from the child to the parent. Only rows with the same cluster key can be placed in the same block. Also, clusters are defined with a sizing factor that tells them how long the standard cluster row (the avg length of all of the data for each cluster key). The cluster will only store multiples of this sizing factor in each block. For example, if you have 4K blocks, and have defined the avg cluster row to be 1K, you can only store 4 cluster records in the block, even if each record actually takes less than 1K (ie due to VARCHAR2s). This can lead to 'wasted' space. Another drawback for clusters is that any full table scans on only one of the tables in the cluster is slower, this is because the DB has to read the whole block, which includes the data from the other cluster tables. From my understanding, the cluster key should be as descriminating as possible, usually the PK of one of the tables, and an FK to the main table in the others.
As you mentioned, clustering one table does not seem to make sense. Nor does using clusters when there are only 60 distinct values.
Ken Atkins - Oracle Programmer/Analyst (katkins_at_iccom.com)
In an effort to contribute some content to the Web I have created an 'Oracle Hint of the Week' site:
http://www.iccom.com/usrwww/katkins/orahint.htm
Check it out!
On Wednesday, June 05, 1996, James Peterson wrote...
>
>
> Greetings,
>
> We are running Oracle 7.2.3 on HP-UX 9.4.
>
> We have a 15G table which experiences very large batch inserts
> and deletes. Delete performance is very poor. The index seems
> to grow excessively.
>
> It has been suggested that we can improve delete performance and index
> efficiency by creating a cluster which includes only this table. The
> cluster key would be the two columns by which we do the delete.
>
> The suggested cluster key is very non-discriminating. We might have
> a million rows in the table and only 60 distinct values for the
suggested
> key. The distribution of rowids across key values is uneven.
>
> ===
> My understanding is that clusters are intended to improve join
> performance. A cluster of one table does not achieve that. I also
> understand that a cluster key should be selected such that all the
rowids
> for a given key value fit in a single block. The suggested key is not
> selective enough to do that.
>
> ===
> Does anybody have any experience using clusters? Can they help batch
> insert and delete operations? Improve index efficiency? Do they cause
> additional maintenance problems? Does a cluster of one table make
sense?
>
> Any comments at all are appreciated.
>
> Thanks,
>
> Jim Peterson
> --
>
> =============================
>
> Jim Peterson
> TDS Computing Services
> Madison, WI USA
> james.peterson_at_teldta.com
>
>
>
Received on Mon Jun 10 1996 - 00:00:00 CEST