Re: Need help with cost based optimizer "logic"

Try a functional index:

http://www.sql.org/sql-database/postgresql/manual/indexes-functional.html

"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)
>
>
Received on Tue Apr 12 2005 - 09:53:39 CDT