Hello there,
Good Day!
I have a query at hand:
SELECT mf_trn_id
FROM mf_transactions
WHERE MF_TRN_AGENT_CD = :b1
AND MF_TRN_PAN_NO = :b2
AND MF_TRN_SCH_CD = :b3
AND MF_TRN_COMP_CD = :b4
AND MF_TRN_CD = :b5
AND MF_TRN_FOLIO = Decode(:b5, 'P', mf_trn_folio, :b7)
AND Nvl(MF_TRN_AMT, 0) = Decode(:b5, 'P', To_number(:b9), Nvl(mf_trn_amt, 0))
AND MF_TRN_STATUS_CD = 'O'
AND Nvl(mf_trn_off_map_flg, 'N') = 'Y'
AND MF_TRN_TRUNC_DT BETWEEN Trunc( SYSDATE - 20 ) AND Trunc(SYSDATE)
AND ROWNUM = 1;
Here,
<b>
AND MF_TRN_FOLIO = Decode(:b5, 'P', mf_trn_folio, :b7)
</b>
is the culprit, wherever we pass :b5 as 'P'. Actually oracle has generated a smart logic kind of plan with concatenation:
----------------------------------------------------------------------------------------
| Id | Pid | Ord | Operation | Name | E-Rows |
----------------------------------------------------------------------------------------
| 0 | | 11 | SELECT STATEMENT | | |
|* 1 | 0 | 10 | COUNT STOPKEY | | |
| 2 | 1 | 9 | CONCATENATION | | |
|* 3 | 2 | 4 | FILTER | | |
|* 4 | 3 | 3 | FILTER | | |
|* 5 | 4 | 2 | TABLE ACCESS BY INDEX ROWID| MF_TRANSACTIONS | 1 |
|* 6 | 5 | 1 | INDEX RANGE SCAN | MF_TRANSACTIONS_INDX5 | 288 |
|* 7 | 2 | 8 | FILTER | | |
|* 8 | 7 | 7 | FILTER | | |
|* 9 | 8 | 6 | TABLE ACCESS BY INDEX ROWID| MF_TRANSACTIONS | 1 |
|* 10 | 9 | 5 | INDEX RANGE SCAN | IDX_MF_TRANSACTIONS_NEW | 1 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
3 - filter(ROWNUM=1)
4 - filter((TRUNC(SYSDATE@!)>=TRUNC(SYSDATE@!-20) AND
SYS_OP_MAP_NONNULL(:B5)=HEXTORAW('5000') ))
5 - filter(("MF_TRN_PAN_NO"=:B2 AND "MF_TRN_AGENT_CD"=:B1 AND
"MF_TRN_CD"=:B5 AND "MF_TRN_STATUS_CD"='O' AND
"MF_TRN_TRUNC_DT">=TRUNC(SYSDATE@!-20) AND
NVL("MF_TRN_AMT",0)=DECODE(:B5,'P',TO_NUMBER(TO_CHAR(:B9)),NVL("MF_TRN_A
MT",0)) AND "MF_TRN_FOLIO" IS NOT NULL AND
NVL("MF_TRN_OFF_MAP_FLG",'N')='Y' AND
"MF_TRN_TRUNC_DT"<=TRUNC(SYSDATE@!)))
6 - access("MF_TRN_COMP_CD"=:B4 AND "MF_TRN_SCH_CD"=:B3)
7 - filter(ROWNUM=1)
8 - filter((TRUNC(SYSDATE@!)>=TRUNC(SYSDATE@!-20) AND
SYS_OP_MAP_NONNULL(:B5)<>HEXTORAW('5000') ))
9 - filter(("MF_TRN_PAN_NO"=:B2 AND "MF_TRN_AGENT_CD"=:B1 AND
"MF_TRN_CD"=:B5 AND "MF_TRN_STATUS_CD"='O' AND
"MF_TRN_TRUNC_DT">=TRUNC(SYSDATE@!-20) AND
NVL("MF_TRN_AMT",0)=DECODE(:B5,'P',TO_NUMBER(TO_CHAR(:B9)),NVL("MF_TRN_A
MT",0)) AND NVL("MF_TRN_OFF_MAP_FLG",'N')='Y' AND
"MF_TRN_TRUNC_DT"<=TRUNC(SYSDATE@!)))
10 - access("MF_TRN_FOLIO"=:B7 AND "MF_TRN_SCH_CD"=:B3 AND
"MF_TRN_COMP_CD"=:B4)
I am thinking that this predicate is actually not providing any value when 'P' is passed as Bind.
When I change the query to just this:
SELECT mf_trn_id
FROM mf_transactions
WHERE 1=1
and MF_TRN_AGENT_CD = :b1
AND MF_TRN_PAN_NO = :b2
AND MF_TRN_SCH_CD = :b3
AND MF_TRN_COMP_CD = :b4
AND MF_TRN_CD = :b5
AND MF_TRN_FOLIO = :b7
AND Nvl(MF_TRN_AMT, 0) = Decode(:b5, 'P', To_number(:b9), Nvl(mf_trn_amt, 0))
AND MF_TRN_STATUS_CD = 'O'
AND Nvl(mf_trn_off_map_flg, 'N') = 'Y'
AND MF_TRN_TRUNC_DT BETWEEN Trunc( SYSDATE - 20 ) AND Trunc(SYSDATE)
AND ROWNUM = 1;
And the performance is equivalent, whatever I pass as Bind (of course with result being similar in all tests of which I can think of...).
But I am not sure whether both the queries are semantically equivalent or not. What are your thoughts on this? Have you faced such a query in past (I am sure you must had :) )? How you tackled it? I highly appreciate your inputs.
Regards,
Kaushal Ruparel.
Provided :b5 is something other than P, the queries are equivalent. In fact, you could go further and remove this condition too:
AND Nvl(MF_TRN_AMT, 0) = Decode(:b5, 'P', To_number(:b9), Nvl(mf_trn_amt, 0))
But if :b5 is P, then they're different!
There are a couple of ways you could approach this.
1. Have two separate static SQL statements.
This checks the value of :b5. If it's P, it executes one query. If it's not it does the other.
if :b5 = 'P' then
select ... from ...
where mf_trn_cd = :b5;
else
select ... from ...
where mf_trn_cd = :b5
and mf_trn_folio = :b7;
end if;
2. Use dynamic SQL.
Build your SQL statement based on the bind values passed. e.g.
where_clause := 'where ...';
if :b5 = 'P' then
where_clause := where_clause || ' and mf_trn_folio = :b7 ';
end if;
If you want to know more about dynamic SQL, read:
http://docs.oracle.com/database/121/LNPLS/dynamic.htm#LNPLS011 http://www.oracle.com/technetwork/issue-archive/2009/09-jul/o49asktom-090487.html http://www.oracle.com/technetwork/issue-archive/o64sql-095035.html http://www.oracle.com/technetwork/issue-archive/2015/15-may/o35plsql-2541606.html http://www.orafaq.com/wiki/Dynamic_SQL Or you could ignore the above and stick with what you've got :)
As you've noted, the optimizer is smart. The branches under the concatenation are mutually exclusive. So it'll only execute one of these.
If changing the SQL isn't making any difference to performance, why make work for yourself?