Re: Need help with cost based optimizer "logic"

"Syltrem" <syltremzulu_at_videotron.ca> wrote in message news:cbv7e.1106$g4.23378_at_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
>

I'd have to take a closer look at the details to give you a definite answer, but the first guess would be related to the clustering factor - the A+B clustering factor may be very much larger than the A+B+C clustering factor. However, even though the COST of the query could easily be lower for the 'wrong' index in cases like that, I wouldn't normally expect the choice of the wrong index to make the query run faster unless we were looking at a big table and a busy system.

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 April 5th 2005 Received on Thu Apr 14 2005 - 09:57:49 CDT