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