Re: Bind variables and function based index

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_at_gmail.com>:
>
> 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_at_gmail.com> 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_at_gmail.com>
> > 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
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l