Re: Clustering Problem

In article <388d17$pac_at_athos.cc.bellcore.com> mjk_at_spanky.cc.bellcore.com (25994-kelly) writes:
>From: mjk_at_spanky.cc.bellcore.com (25994-kelly)
>Subject: Clustering Problem
>Date: 21 Oct 1994 12:43:19 GMT
 

>With Oracle 7.0.13 and Solaris, I tried clustering one table on a single field. 85% of the records have one value for this field and 15% have the other value. TThe application only uses the 15% almost all the time so I expected the clustering to drastica>With Oracle 7.0.13 and Solaris, I tried clustering one table on a single field. 85% of the records have one value for this field and 15% have the other value. TThe application only uses the 15% almost all the time so I expected the clustering to drastica>
 ly reduce the elapsed time for their procesing, which is all scan type not inquiry on a single record. It worked but I find that when I use viewsfor some other reports, Oracle goes into a CPU loop for hours. Anyone else see this problem? There is no
>al I/O going on in the period. I tried SQL trace which showed the plan it was following. It seemed reasonable.

Don't cluster to do this. Instead change the approach so that your two values are null and a value. Make the 85% one null. Then create an index on the column. The index will not include any entries for the rows with null in that column and so will be fairly small. Then in the where clause specify that value so the index will be used. The bigger the ratio the better this technique works. Received on Thu Oct 27 1994 - 18:37:33 CET