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@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
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      0   SORT (ORDER BY)
      0    CONCATENATION
      0     FILTER
      0      NESTED LOOPS (OUTER)
      0       NESTED LOOPS (OUTER)
      0        NESTED LOOPS
      0         NESTED LOOPS
      0          NESTED LOOPS
     22           NESTED LOOPS
     22            TABLE ACCESS   MODE: ANALYZED (FULL) OF
                       'MTL_CATEGORIES_B' (TABLE)
     22            INDEX   MODE: ANALYZED (UNIQUE SCAN) OF
                       'MTL_CATEGORIES_TL_U1' (INDEX (UNIQUE))
      0           TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID)
                      OF 'PO_REQUISITION_LINES_ALL' (TABLE)
1585746            INDEX   MODE: ANALYZED (RANGE SCAN) OF
                       'PO_REQUISITION_LINES_N2' (INDEX)
      0          TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
                     'PO_REQUISITION_HEADERS_ALL' (TABLE)
      0           INDEX   MODE: ANALYZED (UNIQUE SCAN) OF
                      'PO_REQUISITION_HEADERS_U1' (INDEX (UNIQUE))
      0         TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
                    'PO_REQ_DISTRIBUTIONS_ALL' (TABLE)
      0          INDEX   MODE: ANALYZED (RANGE SCAN) OF
                     'PO_REQ_DISTRIBUTIONS_N1' (INDEX)
      0        TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
                   'PO_DISTRIBUTIONS_ALL' (TABLE)
      0         INDEX   MODE: ANALYZED (RANGE SCAN) OF
                    'PO_DISTRIBUTIONS_N2' (INDEX)
      0       TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
                  'PO_HEADERS_ALL' (TABLE)
      0        INDEX   MODE: ANALYZED (UNIQUE SCAN) OF
                   'PO_HEADERS_U1' (INDEX (UNIQUE))
      0     FILTER
      0      NESTED LOOPS (OUTER)
      0       NESTED LOOPS (OUTER)
      0        NESTED LOOPS
      0         NESTED LOOPS
      0          NESTED LOOPS
      0           NESTED LOOPS
      0            TABLE ACCESS   MODE: ANALYZED (FULL) OF
                       'MTL_CATEGORIES_B' (TABLE)
      0            INDEX   MODE: ANALYZED (UNIQUE SCAN) OF
                       'MTL_CATEGORIES_TL_U1' (INDEX (UNIQUE))
      0           TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID)
                      OF 'PO_REQUISITION_LINES_ALL' (TABLE)
      0            INDEX   MODE: ANALYZED (RANGE SCAN) OF
                       'PO_REQUISITION_LINES_N2' (INDEX)
      0          TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
                     'PO_REQUISITION_HEADERS_ALL' (TABLE)
      0           INDEX   MODE: ANALYZED (UNIQUE SCAN) OF
                      'PO_REQUISITION_HEADERS_U1' (INDEX (UNIQUE))
      0         TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
                    'PO_REQ_DISTRIBUTIONS_ALL' (TABLE)
      0          INDEX   MODE: ANALYZED (RANGE SCAN) OF
                     'PO_REQ_DISTRIBUTIONS_N1' (INDEX)
      0        TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
                   'PO_DISTRIBUTIONS_ALL' (TABLE)
      0         INDEX   MODE: ANALYZED (RANGE SCAN) OF
                    'PO_DISTRIBUTIONS_N2' (INDEX)
      0       TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
                  'PO_HEADERS_ALL' (TABLE)
      0        INDEX   MODE: ANALYZED (UNIQUE SCAN) OF
                   'PO_HEADERS_U1' (INDEX (UNIQUE))
SELECT STATEMENT---Cost = 52
  SORT-UNIQUE--
    NESTED LOOPS-OUTER--
      NESTED LOOPS-OUTER--
        NESTED LOOPS---
          NESTED LOOPS---
            NESTED LOOPS---
              NESTED LOOPS---
                TABLE ACCESS-FULL-MTL_CATEGORIES_B-
                INDEX-UNIQUE SCAN-MTL_CATEGORIES_TL_U1-
              TABLE ACCESS-BY INDEX ROWID-PO_REQUISITION_LINES_ALL-
                INDEX-RANGE SCAN-XXDL_PO_REQUISITION_LINES_F2-
            TABLE ACCESS-BY INDEX ROWID-PO_REQ_DISTRIBUTIONS_ALL-
              INDEX-RANGE SCAN-PO_REQ_DISTRIBUTIONS_N1-
          TABLE ACCESS-BY INDEX ROWID-PO_REQUISITION_HEADERS_ALL-
            INDEX-UNIQUE SCAN-PO_REQUISITION_HEADERS_U1-
        TABLE ACCESS-BY INDEX ROWID-PO_DISTRIBUTIONS_ALL-
          INDEX-RANGE SCAN-PO_DISTRIBUTIONS_N2-
      TABLE ACCESS-BY INDEX ROWID-PO_HEADERS_ALL-
        INDEX-UNIQUE SCAN-PO_HEADERS_U1-

Other related posts: