Re: Index usage

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).