(re): Really Wierd Query tuning issue

From: ryan gaffuri <ryan.gaffuri_at_comcast.net>
Date: Thu, 12 Aug 2004 16:59:19 -0400
Message-ID: <001d01c480af$3d086910$350a5444@ryan2le36ofjce>

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_at_Sun.COM> To: <oracle-l_at_freelists.org>
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_at_freelists.org 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
-----------------------------------------------------------------

Received on Thu Aug 12 2004 - 15:55:25 CDT