Re: Delete on FULL INDEX SCAN - is it inefficient ??

It would be interesting to see how the optimizer got to this choice. The clustering factor suggests the index path is very bad, but if only the first column was considered (by cardinality) in the plan then the index strategy could be quite good.

If you do an explain plan do you get a 'cardinality' figure at all, and is it much higher than the one percent you expect.

Do you want to try a CBO trace (event 10053 at level 12) to see if you can figure out the CBO's rationale ?

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Kevin A Lewis wrote in message ...
>Details of the primary key index as fully scanned
>
>
> UNIQUE Index CROSS__0
>
> Column Name #Distinct
>
> TYPE 14
> NAME 7,141
> PROG 118
> INCLUDE 24,926
>
> Last statistics date 19.07.1999
> Analyze Method Estimate 10%
> Levels of B-Tree 2
> Number of leaf blocks 1,849
> Number of distinct keys 344,130
> Average leaf blocks per key 1
> Average data blocks per key 1
> Clustering factor 91,883
>
>the column_name1 is in fact the TYPE column above and column_name2 is in
>fact the INCLUDE column metioned above. I would think that the number of
>rows to be delete should be less than one percent.
Received on Mon Jul 19 1999 - 11:47:08 CDT