Afternoon,
Could anyone tell me which of the following statements would perform better?
SELECT 1
FROM DUAL
WHERE NVL (NULL, '-1') = NVL (NULL, '-1')
OR
SELECT 1
FROM DUAL
WHERE DECODE(NULL, NULL, '1', '0') = '1'
Both will evaluate nulls as being equal. I was just curious which would perform better as I have to have about 30 of these statements together in a case statement. In my statements the two nulls represent different columns. Right now the decode to me seems to be the better choice considering it rules out the possibility of a replaced '-1', or whatever value I choose to replace null with, being compared as equal to an actual '-1'.
Any other thoughts are always welcome.
you cannot use such simple examples - if you attempt to test those, you'd find the optimizer out smarts you:
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> SELECT 1
2 FROM DUAL
3 WHERE NVL (NULL, '-1') = NVL (NULL, '-1')
4 /
Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> SELECT 1
2 FROM DUAL
3 WHERE DECODE(NULL, NULL, '1', '0') = '1'
4 /
Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
ops$tkyte%ORA11GR2> set autotrace off
so, we have to use something more "real"
so, here is more "real"
ops$tkyte%ORA11GR2> create table t as select subobject_name, object_type from all_objects;
Table created.
ops$tkyte%ORA11GR2> insert into t select * from t;
73000 rows created.
ops$tkyte%ORA11GR2> insert into t select * from t;
146000 rows created.
ops$tkyte%ORA11GR2> insert into t select * from t;
292000 rows created.
ops$tkyte%ORA11GR2> insert into t select * from t;
584000 rows created.
ops$tkyte%ORA11GR2> insert into t select * from t;
1168000 rows created.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> @trace
ops$tkyte%ORA11GR2> alter session set events '10046 trace name context forever, level 12';
Session altered.
ops$tkyte%ORA11GR2> select 1 from t where nvl(subobject_name,'-1') = nvl( object_type, '-1' );
no rows selected
ops$tkyte%ORA11GR2> select 1 from t where decode( subobject_name, object_type, '1', '0' ) = '1';
no rows selected
we'll compare the two approaches for a few million rows... but wait, let me add another:
ops$tkyte%ORA11GR2> select 1 from t where (subobject_name = object_type or (subobject_name is null AND object_type is null));
no rows selected
a tad more verbose, but hey - we are coders, that is what we are paid to do.... write code :)
and tkprof says:
select 1
from
t where nvl(subobject_name,'-1') = nvl( object_type, '-1' )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.29 0.29 1482 4895 1 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.29 0.29 1482 4895 1 0
********************************************************************************
select 1
from
t where decode( subobject_name, object_type, '1', '0' ) = '1'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.20 0.20 57 4895 1 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.20 0.20 57 4895 1 0
********************************************************************************
select 1
from
t where (subobject_name = object_type or (subobject_name is null AND
object_type is null))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.11 0.11 3 4895 1 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.11 0.11 3 4895 1 0
#3 seems best - AND you don't have to come up with some "fake value" - no -1's or aything like that. works with all data...