RE: Bind variables and function based index

Hello Kumar,
in the bad plan CBO is splitting the SQL in two SELECTs, merging the result sets (CONCATENATION operator) in the end . Do you have histogram on PO_REQUISITION_LINES_ALL .SUGGESTED_VENDOR_NAME column ? If yes, try to gather statistics on this column without histograms -> DBMS_STATS.GATHER_TABLE_STATS(.. TABNAME=>'PO_REQUISITION_LINES_ALL ',... METHOD_OPT=>'for column SUGGESTED_VENDOR_NAME size1 '). Alternatively you can gather statistics on the "virtual coulumns" e.g. columns, participating in FBI: DBMS_STATS.GATHER_TABLE_STATS(..TABNAME=>'PO_REQUISITION_LINES_ALL ',... METHOD_OPT=>'for all hidden columns size 1 ').  

Best Regards. Milen          

 <mailto:ksmadduri_at_gmail.com>

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

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kumar Madduri Sent: Thursday, May 08, 2008 6:57 AM
To: oracle-l_at_freelists.org
Subject: Bind variables and function based index

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 - 04:29:26 CDT