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@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@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@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@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@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: bhabani s pradhan
INET: bhabaniindia_at_rediffmail.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).
Received on Thu Dec 25 2003 - 06:09:25 CST
