RE: Bind variables and function based index
- From: "Milen Kulev" <makulev@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 8 May 2008 11:29:26 +0200
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@xxxxxxxxx>
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Kumar Madduri
Sent: Thursday, May 08, 2008 6:57 AM
To: oracle-l@xxxxxxxxxxxxx
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@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:
