RE: Bind variables and function based index

Hello Kumar,
 could you send us please the real execution plan(s) , extracted from the shared pool: select * from table(dbms_xplan.display_cursor(<sql_id>,<child#>,'+PEEKED_BINDS'));    

Perhaps you have s bind peeking issue...  

Best Regards. Milen  

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Á¶µ¿¿í Sent: Thursday, May 08, 2008 7:31 AM
To: ksmadduri_at_gmail.com
Cc: oracle-l_at_freelists.org
Subject: 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 <http://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 Received on Thu May 08 2008 - 05:05:06 CDT