Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Need help with cost based optimizer "logic"

Re: Need help with cost based optimizer "logic"

From: Kevin Crosbie <caoimhinocrosbai_at_yahoo.com>
Date: Tue, 12 Apr 2005 18:15:35 +0200
Message-ID: <1113322547.4f439d5b57f9491e7a8dc6762a2b72c8@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? 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.

"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)
> >
> >
>
>
Received on Tue Apr 12 2005 - 11:15:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US