Re: Need help with cost based optimizer "logic"

"Syltrem" <syltremzulu_at_videotron.ca> wrote in message news:ldR6e.1052$g4.22294_at_tor-nn1.netcom.ca...
> Hi
>
> I have a query that runs slow (8.36 seconds) when the predicates includes:
> AND UPPER(A1.PART_CODE) LIKE '1/4%' )
> It does a full table scan even though I have an index on PART_CODE.
> An index scan would be much quicker (the index size is 4MB compared to the
> table's size of 190MB).
>
> When the query ALSO includes
> AND UPPER(A1.PART_CODE) LIKE '%' )
> (which is kind of dumb I know, to have the 2 predicates) then Oracle does
> an
> index range scan and the result is returned in 0.28 seconds.
>
> The 2 queries are generated by a 3rd party application.
>
> The question is:
> Why the change in behavior? I don't understand the logic for choosing the
> INDEX RANGE SCAN path when both predicates are given, and not doing it
> when
> only the first is given, especially when the 2nd predicate is irrelevant
> as
> it will not change the result of the query.
>
> An 10053 trace reveals this:
>
> For the SLOW query (one predicate):
>
> ----------------------------------------------
> Access path: tsc Resc: 2324 Resp: 2324
> Access path: index (scan)
> INDEX#: 804435 TABLE: PRODUCT_MASTER
> CST: 4299 IXSEL: 1.0000e+00 TBSEL: 5.0000e-02
> BEST_CST: 2324.00 PATH: 2 Degree: 1
>
> For the QUICK query (with the 2 predicates)
> ----------------------------------------------
> Access path: tsc Resc: 2324 Resp: 2324
> Access path: index (scan)
> INDEX#: 804435 TABLE: PRODUCT_MASTER
> CST: 695 IXSEL: 1.0000e+00 TBSEL: 2.5000e-03
> BEST_CST: 695.00 PATH: 4 Degree: 1
>
>
> The value of TBSEL differ in the 2, but what does it mean? And why is it
> different between the 2 queries?
>
> Thanks for any input on how to interpret the optimizer calculations in
> regards to the change in the query predicates.
>
> --
> Syltrem
>
> http://pages.infinit.net/syltrem (OpenVMS related web site, en français)
>
>

Without seeing the full trace, and the table definition, and running a check on the appropriate version:-

The optimizer has given the predicate

    UPPER(A1.PART_CODE) LIKE '%' )
a selectivity of 5%. (5 * 10e-2)

It has also given the predicate

    UPPER(A1.PART_CODE) LIKE '%' )
a selectivity of 5% (5 * 10e-2)

Selectivity of (A and B) =
selectivity(A) * selectivity(B) =

    25 * 10e-4 = 2.5*10e-3 QED

The optimizer has then decided to scan the entire index (IXSEL = 1), knowing that
it can apply the filter predicate(s) in the index before visiting that 0.25% of the table.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated March 9th 2005