Query is query. The important thing is cost and cardinality. Post your explain plan for both cases, using "select * from table(dbms_xplan.display(null,null,'all'))"; (if you're on 10g) or "select * from table(dbms_xplan.display)" (on 9i) Dion Cho 2008/5/8, Kumar Madduri <ksmadduri@xxxxxxxxx>: > > Hi List, > In continuation with my earlier post > This is the query. Couple of things I noticed about this is that this > query does not use the Function based index created on suggested_vendor_name > and also the explain plan repeats itslef (it does the same steps twice). > Attached is the explain plan so that you see what I mean. > > On the other hand if I replace these bind variables with actual values, it > does use the index and no repeatition (in explain plan) > > SELECT > RH.SEGMENT1 REQUISITION_NUMBER, > XXDL_REQ_WEB_INQUIRY.DECODE_PERSON_NAME(RH.PREPARER_ID) ORIGINATOR_NAME, > > > RL.SUGGESTED_VENDOR_NAME, RH.CREATION_DATE, RH.AUTHORIZATION_STATUS, > DECODE(NVL(PH.PO_HEADER_ID,'1'),'1',' ',NVL(PH.AUTHORIZATION_STATUS, > 'INCOMPLETE')) PO_STATUS > FROM > PO.PO_REQUISITION_LINES_ALL RL, PO.PO_REQUISITION_HEADERS_ALL RH, > > > PO.PO_REQ_DISTRIBUTIONS_ALL RD, PO.PO_DISTRIBUTIONS_ALL PD, > PO.PO_HEADERS_ALL PH, MTL_CATEGORIES MC WHERE 1=1 AND > UPPER(RL.SUGGESTED_VENDOR_NAME) LIKE UPPER(:B7 || '%') AND > RH.REQUISITION_HEADER_ID = RL.REQUISITION_HEADER_ID+0 AND > > > RL.REQUISITION_LINE_ID = RD.REQUISITION_LINE_ID AND RD.DISTRIBUTION_ID = > PD.REQ_DISTRIBUTION_ID(+) AND RD.EXPENDITURE_TYPE = NVL(:B6 , > RD.EXPENDITURE_TYPE) AND PD.PO_HEADER_ID = PH.PO_HEADER_ID(+) AND > RH.CREATION_DATE BETWEEN :B5 AND :B4 AND RH.AUTHORIZATION_STATUS=DECODE(:B3 > > > ,'1',RH.AUTHORIZATION_STATUS,:B3 ) AND RL.CATEGORY_ID = MC.CATEGORY_ID AND > MC.SEGMENT1||'' = NVL(:B2 ,MC.SEGMENT1) AND MC.SEGMENT2||'' = NVL(:B1 , > MC.SEGMENT2) AND ((MC.ATTRIBUTE9 = 'Y') OR (RH.PREPARER_ID = > > > FND_GLOBAL.EMPLOYEE_ID) OR > (XXDL_REQ_WEB_INQUIRY.ISAPPROVER(RH.REQUISITION_HEADER_ID, > FND_GLOBAL.EMPLOYEE_ID) = 'Y')) ORDER BY RH.CREATION_DATE DESC > > > > On Tue, May 6, 2008 at 5:56 PM, Mark Brinsmead < > pythianbrinsmead@xxxxxxxxx> wrote: > > > Using bind variables rather than literals can alter the cardinality > > estimates used by the CBO, and that can alter the execution plan. > > > > It might be helpful to provide the actual query, or at least the > > execution plans. > > > > > > > > On Tue, May 6, 2008 at 5:54 PM, Kumar Madduri <ksmadduri@xxxxxxxxx> > > wrote: > > > > > Hi > > > Is there any restriction with bind variables and function based > > > indexes. I noticed that if I have literals fbi is being used but if I > > > replace that with bind variables then fbi is not used. DB version is > > > 10.2.0.1. You cant seem to force it to use FBI through hints also. As > > > I am investigating this, I thought of posting here to see if there are any > > > known issues or restrictions that prevent function based indexes usage > > > with > > > bind variables. > > > QUERY_REWRITE_INTEGRITY is set to trusted and query_rewrite_enabled is > > > true. > > > > > > thank you > > > - kumar > > > > > > > > > > > -- > > Cheers, > > -- Mark Brinsmead > > Senior DBA, > > The Pythian Group > > http://www.pythian.com/blogs > > > > >