Re: Need help with cost based optimizer "logic"

"Kevin Crosbie" <caoimhinocrosbai_at_yahoo.com> a écrit dans le message de news:1113322547.4f439d5b57f9491e7a8dc6762a2b72c8_at_teranews...
> Should have googled more carefully... I just noticed that the link is for
> Postgres! But you get the idea anyway.
>
> You'll have to give a more reproducible example, what other predicates did
> you include, can you give a full explain plan for the two queries...
>
> Don't know why it's doing a Range Scan when you do UPPER(PART_CODE) like
> '%'. Does it work when you only have this clause?

Do you mean that Oracle is smart enough to see that LIKE '%' means "anything" and thus it can ignore the UPPER function?

> But it does make sense that it does a full table scan when you do
> UPPER(PART_CODE) like '1/4%' because you are no longer including anything
> that is indexed in your clause. If you use a functional based index,
you
> do have something to lookup against an index.
>

As opposed to here where it cannot ignore the UPPER as '1/4%' really is "something" ?

If this is so, that would explain why it would try the index scan to find "anything", then only keep the ones that match "something". But is it really that smart as to have special logic for LIKE '%' ?

-- 
Syltrem

http://pages.infinit.net/syltrem (OpenVMS related web site, en français)






> "Kevin Crosbie" <caoimhinocrosbai_at_yahoo.com> wrote in message

> news:1113317629.6036914f3560cecbc6980b78fcdc04a3_at_teranews...

> > 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)


> > >

> > >

> >

> >

>

>