Use the decode Function

Decode Function usage:

OracleOfDecodeThe function is quite interesting.OracleExclusive, International StandardSQLNot inDecodeFunction.

Syntax

Decode (Col | expression, search1, result1

[, Search2, result2 ,...,]

[, Default])

Example

Select product_id,

Decode (warehouse_id, 1, 'southlake ',

2, 'san Francisco ',

3, 'new Jersey ',

4, 'seattle ',

'Nonomestic ') "location"

From inventories

Where product_id: <1775

Order by product_id, "location ";

The preceding simple syntax and examples show thatDecodeFunctions can also be used for judgment and can be implemented.Case... When... Then... Else... end
AndIf... then... else... end ifThe same features.

Decode Function advantages:

1,
UseDecodeThe function can avoid scanning the same record repeatedly or joining the same table repeatedly, thus reducing the data processing time.

For exampleScottUserEMPDepartments in the table20And Department30The number of employees and the cost of each department.

 

Select count (*),

Sum (SAL) fromEMP where deptno = 20;

Select count (*),

Sum (SAL) fromEMP where deptno = 30;

The preceding statement can be combined into one to achieve the same purpose.

Select count (decode (deptno, 20, 'x', null) d20_count,

Count (decode (deptno, 30, 'x', null) d30_count,

Sum (decode (deptno, 20, Sal, null) d20_sal,

Sum (decode (deptno, 30, Sal, null) d30_sal

From EMP;

2,
SimplifiedCode

In the preceding exampleCase when
To write more code.

Select product_id,

Case warehouse_id

When 1 then 'southlake'

When 2 then 'san Francisco'

When 3 then 'new Jersey'

When 4 then 'seattle'

Else 'non domestic'

End as "location"

From inventories

Where product_id: <1775

Order by product_id, "location ";

Suggestion:For simple judgment and useDecodeSimple and clear functions

Decode Function Performance Comparison Case when Performance

If... then... end ifThis is a backward judgment method. We will not compare it here.

I often see on the Internet how efficient a function or a certain writing method is, and how to test the execution efficiency:

SQL> Create Table T
2 select .*
3 from dba_objects A, dba_mviews;

Table created.

SQL> select count (*) from T;

Count (*)
----------
6075760

Check belowDecodeAndCaseStatement efficiency:

SQL & gt; Set array 1000
SQL> set timing on
SQL> set autot trace
SQL> select decode (owner, 'system', 'system', 'sys ', 'system', 'user ')
2 from T;

6075760 rows selected.

Elapsed: 00:00:07. 24

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
--------------------------------------------------------------------------
| 0 | SELECT statement | 4245k | 68m | 13828 (1) | 00:03:14 |
| 1 | table access full | T | 1275k | 68m | 13828 (1) | 00:03:14 |
--------------------------------------------------------------------------

Note
-----
-Dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
47551 consistent gets
0 physical reads
0 redo size
46288564 bytes sent via SQL * Net to client
67317 bytes encoded ed via SQL * Net From Client
6077 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
6075760 rows processed

SQL> select Case Owner when 'system'

2 when 'sys 'then 'system'
3 else 'user' end
4 From t;

6075760 rows selected.

Elapsed: 00:00:07. 22

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
--------------------------------------------------------------------------
| 0 | SELECT statement | 4245k | 68m | 13828 (1) | 00:03:14 |
| 1 | table access full | T | 1275k | 68m | 13828 (1) | 00:03:14 |
--------------------------------------------------------------------------

Note
-----
-Dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
47551 consistent gets
0 physical reads
0 redo size
46288578 bytes sent via SQL * Net to client
67317 bytes encoded ed via SQL * Net From Client
6077 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
6075760 rows processed

SQL> select case when owner = 'system' then 'system'

2 when owner = 'sys 'then 'system'
3 else 'user' end
4 From t;

6075760 rows selected.

Elapsed: 00:00:07. 23

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
--------------------------------------------------------------------------
| 0 | SELECT statement | 4245k | 68m | 13828 (1) | 00:03:14 |
| 1 | table access full | T | 1275k | 68m | 13828 (1) | 00:03:14 |
--------------------------------------------------------------------------

Note
-----
-Dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
47551 consistent gets
0 physical reads
0 redo size
46288585 bytes sent via SQL * Net to client
67317 bytes encoded ed via SQL * Net From Client
6077 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
6075760 rows processed

Test result:CaseIs the most efficient in writing simple expressions.CaseAnother way of writing,DecodeThe efficiency is the lowest. However,600 millionThe final result is only0.01To0.02Second query, there is no way to draw the above conclusion, because the difference is too small, so that any other impact is enough to change the test results, if you need to draw a conclusion, then the conclusion is3The efficiency is basically the same.

However, becauseCaseThe expression is more flexible, making the previousDecodeSome of the techniques that must be used can be simplified.CaseTo improve the performance, for example:

SQL> select decode (sign (object_id), 1, '+',-1, '-', '0 ')

2 from T;

6075760 rows selected.

Elapsed: 00:00:04. 94

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
--------------------------------------------------------------------------
| 0 | SELECT statement | 4245k | 52m | 13840 (1) | 00:03:14 |
| 1 | table access full | T | rj5k | 52m | 13840 (1) | 00:03:14 |
--------------------------------------------------------------------------

Note
-----
-Dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
47551 consistent gets
0 physical reads
0 redo size
31491431 bytes sent via SQL * Net to client
67317 bytes encoded ed via SQL * Net From Client
6077 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
6075760 rows processed

SQL> select case when object_id> 0 then '+'
2 when object_id <0 then '-'
3 else '0' end
4 From t;

6075760 rows selected.

Elapsed: 00:00:04. 60

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
--------------------------------------------------------------------------
| 0 | SELECT statement | 4245k | 52m | 13840 (1) | 00:03:14 |
| 1 | table access full | T | rj5k | 52m | 13840 (1) | 00:03:14 |
--------------------------------------------------------------------------

Note
-----
-Dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
47551 consistent gets
0 physical reads
0 redo size
31491449 bytes sent via SQL * Net to client
67317 bytes encoded ed via SQL * Net From Client
6077 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
6075760 rows processed

HereCaseThe main reason for performance improvement isCaseAvoidedSignFunction call, notCaseIts own performance is higherDecodeIn fact, if you useSignAnd useCaseThe so-called efficient Syntax:

SQL> select case sign (object_id) when 1 then '+'
2 when-1 then '-'
3 else '0' end
4 From t;

6075760 rows selected.

Elapsed: 00:00:04. 97

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
--------------------------------------------------------------------------
| 0 | SELECT statement | 4245k | 52m | 13840 (1) | 00:03:14 |
| 1 | table access full | T | rj5k | 52m | 13840 (1) | 00:03:14 |
--------------------------------------------------------------------------

Note
-----
-Dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
47551 consistent gets
0 physical reads
0 redo size
31491445 bytes sent via SQL * Net to client
67317 bytes encoded ed via SQL * Net From Client
6077 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
6075760 rows processed

We can see that the efficiency ratio isDecodeLow.

 

Summary

 

1,
CaseStatement andDecodeFunction execution efficiency

Whether it isDecodeOrCaseThere is no obvious difference in execution efficiency between the two methods.

2,
Code Implementation

UseDecodeThe function can avoid scanning the same record repeatedly or joining the same table repeatedly, thus reducing the data processing time.

For simple judgment and useDecodeThe function is simple and clear.

 

Test and organize the above documents from the Internet

Official Reference documents:

Oracle Database

SQL Language Reference

11GRelease 2 (11.2)

E17118-04

5 FunctionsÀDecode