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-