On Jun 14, 2:41 am, kazim hooshmand <[email protected]> wrote:
> sample code for decode function:
>
> count1>Decode(count2,NULL,0,count2)
>
> it will do the nvl() task as well as extra features for case, and make
> the coding shorter
>
> thanks
Your post makes very little sense as nvl() is better suited to the
task you posted as it creates more work for Oracle:
SQL> select * from decode_nonsense
2 where count1 > nvl(count2,0);
COUNT1 COUNT2
---------- ----------
11
22
33
44
55
66
77
88
99
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 245038917
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 234 | 3
(0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DECODE_NONSENSE | 9 | 234 | 3
(0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COUNT1">NVL("COUNT2",0))
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
48 recursive calls
0 db block gets
20 consistent gets
0 physical reads
0 redo size
561 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
SQL>
SQL> select * from decode_nonsense
2 where count1>Decode(count2,NULL,0,count2);
COUNT1 COUNT2
---------- ----------
11
22
33
44
55
66
77
88
99
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 245038917
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 234 | 3
(0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DECODE_NONSENSE | 9 | 234 | 3
(0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COUNT1">DECODE(TO_CHAR("COUNT2"),NULL,0,"COUNT2"))
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
561 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
SQL>
NVL() works without the implicit conversion your 'improvement'
requires and CASE is far more flexible. I think this is a situation
where the paint covers the flaws.
David Fitzjarrell
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
