(re): Really Wierd Query tuning issue

  • From: "ryan gaffuri" <ryan.gaffuri@xxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 12 Aug 2004 16:59:19 -0400

yup.. over and over again. made sure it was compiled. Wierd thing is that I
get a reduction in LIOs with the same plan if use an 'order by ' on the
value in the index.

really wierd.

clustering factor should not be a factor in a 56 row table since all the
index data should fit in one block. Its just 1 number field in the index.
----- Original Message ----- 
From: "Daniel Fink" <Daniel.Fink@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, August 12, 2004 3:41 PM
Subject: Re: Really Wierd Query tuning issue

> Ryan,
> Did you run the second query shortly after the first? What happens if you
run the first query over and over? Are the logical I/Os of
> the table/index or could they be related to parsing the statement?
> As for clustering factor, IIRC, that comes into play only when you are
dealing with index range or full scans. For a unique scan, it
> will return 1 index entry that points to 1 table row. For other scans, the
CBO uses it to calculate how many table blocks will I
> have to visit if I read a certain number of index entries. For example, a
high clustering factor tells the CBO that the index
> entries returned by the range scan (say 10% of the total) will require
visiting a high number of table blocks (say 80%), so a full
> table scan may be more efficient.
> Regards,
> Daniel

Please see the official ORACLE-L FAQ: http://www.orafaq.com
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Other related posts:

  • » (re): Really Wierd Query tuning issue