To illustrate the concept we will be solving the following problem defined for scott schema:
Find all top paid employees in each department. Display employee names, salaries, jobs, and department.
To qualify for a workaround, a solution’s execution plan should have a distinct hash value (More on that can be found in my book “Oracle SQL Tricks and Workarounds”).
Workaround #1: Correlated subquery
SELECT ename, job, sal, deptno
FROM emp a
WHERE sal=(SELECT MAX(sal)
FROM emp b
WHERE b.deptno=a.deptno);
Result:
ENAME JOB SAL DEPTNO
---------- --------- ---------- ----------
BLAKE MANAGER 2850 30
SCOTT ANALYST 3000 20
KING PRESIDENT 5000 10
FORD ANALYST 3000 20
Execution Plan:
Plan hash value: 1245077725
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 8 (25)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 47 | 8 (25)| 00:00:01 |
| 2 | VIEW | VW_SQ_1 | 3 | 78 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 294 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Workaround #2: Correlated subquery with arithmetic transformation
SELECT ename, job, sal, deptno
FROM emp a
WHERE 0=(SELECT MAX(b.sal)-a.sal
FROM emp b
WHERE b.deptno=a.deptno)
Execution Plan:
Plan hash value: 2649664444
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 24 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | EMP | 14 | 294 | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 7 | | |
|* 4 | TABLE ACCESS FULL| EMP | 5 | 35 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Workaround #3: Non-Correlated subquery
SELECT ename, job, sal, deptno
FROM emp a
WHERE (deptno, sal) IN (SELECT deptno, MAX(sal)
FROM emp
GROUP BY deptno)
Execution Plan:
Plan hash value: 2491199121
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 8 (25)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 1 | 47 | 8 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 294 | 3 (0)| 00:00:01 |
| 3 | VIEW | VW_NSO_1 | 3 | 78 | 4 (25)| 00:00:01 |
| 4 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Workaround #4: Aggregating over Cartesian Product
SELECT a.ename, a.job, a.sal, a.deptno
FROM emp a, emp b
WHERE a.deptno=b.deptno
GROUP BY a.ename, a.job, a.sal, a.deptno
HAVING a.sal=MAX(b.sal)
Execution Plan:
Plan hash value: 2435006919
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 168 | 8 (25)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 6 | 168 | 8 (25)| 00:00:01 |
|* 3 | HASH JOIN | | 65 | 1820 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 294 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Workaround #5: Outer Join with IS NULL check
SELECT a.ename, a.job, a.sal, a.deptno
FROM emp a LEFT JOIN emp b ON a.deptno=b.deptno
AND a.sal<b.sal
WHERE b.empno IS NULL
Execution Plan:
Plan hash value: 1201587841
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 7 (15)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN OUTER | | 1 | 32 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 294 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 154 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Workaround #6: Using NOT EXISTS
SELECT ename, job, sal, deptno
FROM emp a
WHERE NOT EXISTS (SELECT 1
FROM emp b
WHERE b.deptno=a.deptno
AND b.sal>a.sal)
Execution Plan:
Plan hash value: 3353202012
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 140 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 5 | 140 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 294 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Synonymous Workaround for #6 (execution plan has the same hash value): Using COUNT(*)=0 Equivalent
SELECT ename, job, sal, deptno
FROM emp a
WHERE 0=(SELECT COUNT(*)
FROM emp b
WHERE b.deptno=a.deptno
AND b.sal>a.sal)
Execution Plan:
Plan hash value: 3353202012
Workaround #7: Using ALL Predicate
SELECT ename, job, sal, deptno
FROM emp a
WHERE a.sal>=ALL(SELECT b.sal
FROM emp b
WHERE b.deptno=a.deptno)
Execution Plan:
Plan hash value: 2561671593
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 294 | 24 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 294 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 2 | 14 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Workaround #8: Using In-Line View
SELECT a.ename, a.sal, a.deptno
FROM emp a, (SELECT deptno, MAX(sal) max_sal
FROM emp
GROUP BY deptno) b
WHERE a.deptno=b.deptno
AND a.sal=b.max_sal
Execution Plan:
Plan hash value: 269884559
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 8 (25)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 39 | 8 (25)| 00:00:01 |
| 2 | VIEW | | 3 | 78 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Workaround #9: Using EXISTS Predicate
SELECT ename, job, sal, deptno
FROM emp a
WHERE EXISTS (SELECT 1
FROM emp b
WHERE b.deptno=a.deptno
HAVING a.sal=MAX(b.sal))
Execution Plan:
Plan hash value: 3057787348
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 24 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | EMP | 14 | 294 | 3 (0)| 00:00:01 |
|* 3 | FILTER | | | | | |
| 4 | SORT AGGREGATE | | 1 | 7 | | |
|* 5 | TABLE ACCESS FULL| EMP | 5 | 35 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Synonymous Workaround for #9 (execution plan has the same hash value): Using COUNT(*)>0 Equivalent
SELECT ename, job, sal, deptno
FROM emp a
WHERE 0< (SELECT COUNT(1)
FROM emp b
WHERE b.deptno=a.deptno
HAVING a.sal=MAX(b.sal))
Execution Plan:
Plan hash value: 3057787348
Here is a practical example which happens to qualify as another Synonymous Workaround for #9:
SELECT ename, job, sal, deptno
FROM emp a
WHERE NOT EXISTS (SELECT 1
FROM emp b
WHERE b.deptno=a.deptno
HAVING a.sal<MAX(b.sal))
Execution Plan:
Plan hash value: 3057787348
Workaround #10: Using Analytical Function RANK()
WITH x AS (
SELECT ename, job, sal, deptno,
RANK()OVER(PARTITION BY deptno ORDER BY sal DESC) rk
FROM emp a
)
SELECT ename, job, sal, deptno
FROM x
WHERE rk=1
Execution Plan:
Plan hash value: 3291446077
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 728 | 4 (25)| 00:00:01 |
|* 1 | VIEW | | 14 | 728 | 4 (25)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 294 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 294 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Workaround #11: Using Analytical Function MAX
WITH x AS (
SELECT ename, job, sal, deptno,
MAX(sal)OVER(PARTITION BY deptno) max_sal
FROM emp a
)
SELECT ename, job, sal, deptno
FROM x
WHERE sal=max_sal
Execution Plan:
Plan hash value: 4130734685
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 728 | 4 (25)| 00:00:01 |
|* 1 | VIEW | | 14 | 728 | 4 (25)| 00:00:01 |
| 2 | WINDOW SORT | | 14 | 294 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 294 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Workaround #12: Using Analytical Function COUNT with CONNECT BY
WITH x AS (
SELECT ename, job, sal, deptno, COUNT(*)OVER(PARTITION BY empno) cnt
FROM emp a
CONNECT BY deptno=PRIOR deptno
AND sal<PRIOR sal
)
SELECT ename, job, sal, deptno
FROM x
WHERE cnt=1
Execution Plan:
Plan hash value: 704858046
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 728 | 2 (0)| 00:00:01 |
|* 1 | VIEW | | 14 | 728 | 2 (0)| 00:00:01 |
| 2 | WINDOW SORT | | 14 | 350 | 2 (0)| 00:00:01 |
|* 3 | CONNECT BY WITHOUT FILTERING| | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 350 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Workaround #13: Using Analytical Function COUNT with CONNECT BY filtered by LEVEL
WITH x AS (
SELECT ename, job, sal, deptno, COUNT(*)OVER(PARTITION BY empno) cnt
FROM emp a
WHERE level<=2
CONNECT BY deptno=PRIOR deptno
AND sal<PRIOR sal
)
SELECT ename, job, sal, deptno
FROM x
WHERE cnt=1
Execution Plan:
Plan hash value: 2668428643
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 728 | 2 (0)| 00:00:01 |
|* 1 | VIEW | | 14 | 728 | 2 (0)| 00:00:01 |
| 2 | WINDOW SORT | | 14 | 350 | 2 (0)| 00:00:01 |
|* 3 | FILTER | | | | | |
|* 4 | CONNECT BY WITHOUT FILTERING| | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 350 | 2 (0)| 00:00:01 |
| 6 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Workaround #14: CONNECT BY with GROUP BY and HAVING
SELECT ename, job, sal, deptno
FROM emp a
CONNECT BY deptno=PRIOR deptno
AND sal<PRIOR sal
GROUP BY ename, job, sal, deptno
HAVING COUNT(*)=1
Execution Plan:
Plan hash value: 2144516570
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 4 (25)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 21 | 4 (25)| 00:00:01 |
|* 3 | CONNECT BY WITHOUT FILTERING| | | | | |
| 4 | TABLE ACCESS FULL | EMP | 14 | 294 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Workaround #15: GROUP BY and HAVING over CONNECT BY filtered by LEVEL
SELECT ename, job, sal, deptno
FROM emp a
WHERE level<=2
CONNECT BY deptno=PRIOR deptno
AND sal<PRIOR sal
GROUP BY ename, job, sal, deptno
HAVING COUNT(*)=1
Execution Plan:
Plan hash value: 1946770371
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 4 (25)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 21 | 4 (25)| 00:00:01 |
|* 3 | FILTER | | | | | |
|* 4 | CONNECT BY WITHOUT FILTERING| | | | | |
| 5 | TABLE ACCESS FULL | EMP | 14 | 294 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
If you want to learn how to come up with numerous workarounds on your own, check my book “Oracle SQL Tricks and Workarounds” for instructions.