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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Á¶µ¿¿í
Sent: Thursday, May 08, 2008 7:31 AM
To: ksmadduri@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@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 <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

Other related posts: