Re: Re: Index usage

[Tanel Poder]

I think you should start by posting the exact SQL with exact execution plan to us, there's no point in wild guessing...   Tanel.   ----- Original Message ----- From: bhabani s pradhan To: Multiple recipients of list ORACLE-L 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" <[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).