Using DECODE to Reduce Processing:

By using DECODE statement we can avoid scanning the same rows repeatedly or to join the same table repetitively.
For let us take an example by to get the count and sum of salary for employee name like SMITH and from department 20.

To achieve this we can use the below query :

SQL>SELECT COUNT (*) D20_COUNT, SUM (sal) D20_SAL
FROM emp
WHERE deptno = 20 AND ename LIKE ‘SMITH%’;

PLAN_TABLE_OUTPUT

Plan hash value: 2083865914

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (0)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 17 | 3 (0)| 00:00:04 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

2 – filter(“DEPTNO”=20 AND “ENAME” LIKE ‘SMITH%’)

But to achieve more efficiently we can make use of DECODE statement such as below:

SQL>SELECT COUNT(DECODE(DEPTNO,20, ‘X’, NULL))
D20_COUNT,
SUM(DECODE(DEPTNO,20, SAL, NULL))
D20_SAL
FROM EMP
WHERE ENAME LIKE ‘SMITH%’;

PLAN_TABLE_OUTPUT

Plan hash value: 2083865914

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (0)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 17 | 3 (0)| 00:00:04 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

2 – filter(“ENAME” LIKE ‘SMITH%’)

From the above two select statement we can see the difference on the filer that is reduced by using DECODE statement.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s