----- Original Message -----
Sent: Thursday, December 25, 2003 2:09
PM
Subject: Re: Re: Index usage
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).
