Thanks.
Regards,
B S Pradhan
---------------------
On Fri, 26 Dec 2003 zhu chao wrote :
>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" <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).
>
>
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: zhu chao
> INET: chao_ping@vip.163.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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: bhabani s pradhan
INET: bhabaniindia_at_rediffmail.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).
Received on Fri Dec 26 2003 - 07:19:25 CST
