Oracle NVL () and Decode () two functions which performance is better

1. Create a test table and insert a piece of data
Sql> CREATE TABLE test (id int);
Table created.
sql> INSERT INTO test values (1);
1 row created.
Sql> commit;
Commit complete.

2. Create a function, used to delay, to this function will be circulated here 50 million times, will wait
sql> Create or Replace function Sleep_now return number is
2 a number;
3 begin
4 a:=0;
5 while a<50000000
6 loop
7 A: =a+1;
8 End Loop;
9 return A;
Ten end;
11/
Function created.

3. Open the Sqlplus timer
Sql> Set Timing on

4. Use the NVL () function to test whether the value of the ID column in table test is null
Sql> Select NVL (Id,sleep_now ()) from test;
NVL (Id,sleep_now ())
-------------------
1
elapsed:00:00:03.25
Tabular test on a piece of data spent more than 3 seconds to read, explain what the problem, think about it?
OK, I'll just give you a fixed value: Like 10, put it in the NVL, see if it's going to be done right away?
Sql> Select NVL (10,sleep_now ()) from test;
NVL (10,sleep_now ())
-------------------
10
elapsed:00:00:03.33
It's still the same. It takes more than 3 seconds to look up a piece of data, which means that with the NVL () function, even if the value is not empty, the function still reads the Sleep_now () function, which consumes time.

5. Use decode () to test the same data, look at the time of the query
Sql> Select Decode (Id,null,sleep_now (), 1) from test;
DECODE (Id,null,sleep_now (), 1)
-----------------------------
1
elapsed:00:00:00.01

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

immediately found, almost no time, soon Oh!


Also check the fixed value: for example, 10


sql&gt; Select decode (Id,10,sleep_now (), 1) from test;


DECODE (Id,10,sleep_now (), 1)


---------------------------


1


elapsed:00:00:00.01


also immediately find


to find out so quickly that when the decode () function finds a value, it does not read the Sleep_now () function behind it.

6. Comparing the 4th step with the 5th step, the same operation, obviously decode function performance better.

7. Finally, a decode example, how to use good decode ()
TRUNCATE TABLE test;
Begin
For I in 1. 10000 loop
INSERT into test values (4);
Commit
End Loop;
End
/
Sql> Select Id,count (*) from the test group by ID;
ID COUNT (*)
---------- ----------
4 10000
There are 10,000 records in the table test, the ID value is all 4, consider the following a, B two SQL statements, which is relatively better performance?
A Select decode (id,1, ' a ', 2, ' B ', 3, ' C ', 4, ' D ') from test;
B Select decode (id,4, ' D ', 2, ' B ', 3, ' C ', 1, ' A ') from test;
Know the explanation under!!!