Re: Re: Index usage

Hi,

    To see why oracle choose FTS, alter session set events '10053 trace name context forever,level 2';     You can do alter session to change index_adj and optimizer_index_caching to change only your session, or using hint.

Regards
Zhu Chao.

• Original Message ----- To: Multiple recipients of list ORACLE-L Sent: Thursday, December 25, 2003 8:09 PM

Hi All,

Agreed.. and it should behave that way i.e

if (cost of ind1 scan + then based on c1's selection table access for c3) > (direct table access for c1 and c1) then oracle will use FTS with cost based optimization. So, w/o a hint that is expected.

But why it is not picking the index in my case i donot know.

Also, can optimizer_index_cost_adj help? Its 100 now. Also that affects the whole DB, so is there any way to set it for this particular query ?

Thanks for all the inputs.

Regards,
B S Pradhan

On Wed, 24 Dec 2003 Mike Spalinger wrote :
>The difference is that the first query never has to go to the table (because you're selecting a constant 'x'). The second query has to go to the table to filter on c3.
>
>Mike
>
>anu wrote:
>>No.
>> The index should get used. The query result for query 2 is a subset of rows with ta.c1='val1' will get selected. Subset of query 1.
>> So there is no need for a full table scan. The index can be used in the following way :
>> 1) Use index ind1 to get rows with ta.c1='val1' (which is query 1). This can definitely use an index.
>>2) Further filter using ta.c3 = 'val2'
>> Now may be the index is not very selective and the optimizer is going in for a full table scan. What is the cardinality like? It is strange that RULE or index hint is not taking it. Can you try a simple index(ta) hint or send your hint syntax. Can you try the hint on another table to make sure hint is working. I do not know why hint should not work.
>> Good luck.
>>
>>"Daniel W. Fink" <Daniel.Fink_at_Sun.COM> wrote:
>>
>> You answered your own question.
>>
>> ta.c3 is a nonindexed column, this means that the only way to
>> satisfy the
>> predicate is to perform a full table scan. Since this predicate
>> condition forces
>> a full table scan on ta, which will retrieve the ta.c1 column values
>> at the same
>> time, there is no need to use an index. In fact, an additional index
>> access
>> would decrease the query performance.
>>
>> Daniel Fink
>>
>> bhabani s pradhan wrote:
>>
>> > Hi All,
>> >
>> > Merry Christmas to all
>> >
>> > I have this interesting problem..
>> >
>> > For this query index ind1 on (c1,c2) columns is getting used.
>> > SELECT 'x'
>> > FROM tab ta
>> > WHERE ta.c1='val1';
>> > (gives index ind1 range scan)
>> >
>> > But for
>> >
>> > SELECT 'x'
>> > FROM tab ta
>> > WHERE ta.c1='val1'
>> > AND ta.c3 = 'val2';
>> > (gives FTS)
>> > index ind1 is not being used. c3 is a nonindexed column.
>> >
>> > I have already tried index(ta ind1) , RULE hints.
>> >
>> > The table and the index are analyzed.
>> >
>> > What cud be the reason for that?
>> >
>> > Regards,
>> > B S Pradhan
>>
>> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net
>> -- Author: Daniel W. Fink
>> INET: Daniel.Fink_at_Sun.COM
>>
>> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>> San Diego, California -- Mailing list and web hosting services
>> ---------------------------------------------------------------------
>> To REMOVE yourself from this mailing list, send an E-Mail message
>> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>> the message BODY, include a line containing: UNSUB ORACLE-L
>> (or the name of mailing list you want to be removed from). You may
>> also send the HELP command for other information (like subscribing).
>>
>>!
>>
>>------------------------------------------------------------------------
>>Do you Yahoo!?
>>Free Pop-Up Blocker - Get it now <http://us.rd.yahoo.com/slv/mailtag/*http://companion.yahoo.com/>
>
>
>-- Please see the official ORACLE-L FAQ: http://www.orafaq.net
>-- Author: Mike Spalinger
> INET: Michael.Spalinger_at_Sun.COM
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: zhu chao
  INET: chao_ping_at_vip.163.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).