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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
>>
>> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
>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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
