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: Syltrem <syltremzulu_at_videotron.ca>
Date: Thu, 14 Apr 2005 10:34:37 -0400
Message-ID: <cbv7e.1106$g4.23378@tor-nn1.netcom.ca>


Merci - this is what I was looking for

BTW after getting new stats with FOR ALL INDEXED COLUMNS I discovered the query runs much faster but now uses an index I would never thought would be a good one:

Query has COMPANY + PART in the WHERE clause Query does NOT mention the column PART_EAN_NUMBER anywhere (not in select nor in where)
The table has an index on COMPANY + PART_NUMBER so I would expect Oracle to use this or else an FTS
Oracle prefers using another index that has COMPANY + PART_EAN_NUMBER + PART_CODE, and indeed it is much faster. I don't get it. This index is larger in size (it has one more column) so how can an INDEX RANGE SCAN be faster on this index than on the one that only has the 2 columns we have in the WHERE clause?

Query:
SELECT A1.ROWID
FROM PRODUCT_MASTER A1

WHERE (((((A1.COMPANY_CODE=:1
    AND UPPER(A1.PART_CODE) LIKE :2 ESCAPE '\' )
    AND A1.ACTIVATION_STATUS=:3)

    AND A1.SALES_ORDERS_FLAG=:4)
    AND A1.IFA_FLAG_41=:5)
    AND UPPER(A1.PART_DESC_2) LIKE :7 ESCAPE '\' ) ORDER BY A1.COMPANY_CODE ASC,A1.PART_CODE ASC The other columns in the where clause are not part of an index

I really don't understand why Oracle would use and index on A+B+C when the SELECT clause only has A+C and an index on A+C exists.

-- 
Syltrem

http://pages.infinit.net/syltrem (OpenVMS related web site, en français)
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> a écrit dans le message de
news:d3h43g$6dr$1_at_titan.btinternet.com...

>
> "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
>
>
>
>
>
>
Received on Thu Apr 14 2005 - 09:34:37 CDT

Original text of this message

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