Hi all,
We are working on our 11.2.0.3 RAC (on AIX 7.1) database on trying to figure out why a certain repeated query ( batch load) is not using the correct execution plan.
The query itself looks like:
select CATENTRY_ID from CATENTRY where ((PARTNUMBER=:1 ) OR ((0 = :2 ) AND (PARTNUMBER IS NULL))) and ((MEMBER_ID=:3 ) OR ((0 = :4 ) AND (MEMBER_ID IS NULL)));
This query is an IBM Webshere internal query, which therefore is unchangeable.
The table in question has an Index available on PARTNUMBER & MEMBER_ID
The execution plan however looks like
The execution plan of the above statement looks like:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=2038 Card=1 Bytes=23)
1 0 TABLE ACCESS FULL WCSADMIN.CATENTRY (Cost=2038 Card=1 Bytes=23)
So a FTS scan is used where an Index-lookup would be expected.
The values passed to this query are e.g.:
:1 = XA-GED-1068849
:2 = 1
:3 = -6000
:4 = 1
With the part of the WHERE CLAUSE then having ((0=1) AND (PARTNUMBER IS NULL)) and the same for ((0=1) AND (MEMBER_ID IS NULL)) would result in an Index lookup.:
select
catentry_id
from catentry
where ( (partnumber = 'XA-GED-5702810')
or ( (0 = 1)
and (partnumber is null)))
and ( (member_id = -6000)
or ( (0 = 1)
and (member_id is null))) ;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=3 Card=1 Bytes=23)
1 0 TABLE ACCESS BY INDEX ROWID WCSADMIN.CATENTRY (Cost=3 Card=1 Bytes=23)
2 1 INDEX UNIQUE SCAN WCSADMIN.I0000064 (Cost=2 Card=1)
Somewhere in the parsing of the query the optimizer does not have/use all the information needed to determine the correct plan, allthough the tracefile shows all values are captured correctly
I would expect that the optimizer would "PEEK" all available variables to determine the best execution plan.
It looks however that the two BINDs for the "0=:2" and "0=:4" are not "peeked" and therefore not used, which results in a Full Table Scan as the PARTNUMBER IS NULL and MEMBER_ID IS NULL are not skipped.
Can anyone confirm that only BINDs for "existing/real" columns are peeked??
And is this configurable ??
Thanks
FJ Franken