Nuances when dropping an Oracle user, by Zahar Hilkevich

When you need to drop a user who is not currently connected to the database, the task is trivial (please do not try it if you don’t really need to drop your user):

DROP USER SCOTT CASCADE;

With actively used database user schemas,  the above command may fail due to active Oracle sessions opened by that user (ORA-01940: cannot drop a user that is currently connected). So you may want to drop all those sessions first before dropping the user:

DECLARE
    CURSOR c IS
    SELECT sid, serial#
    FROM v$session 
    WHERE username='SCOTT';

    v_sql  VARCHAR2(200):='ALTER SYSTEM KILL SESSION ''{sid},{serial#}'' IMMEDIATE';
    v_kill VARCHAR2(200);
BEGIN
    FOR v IN c LOOP
        v_kill:=REPLACE(v_sql,'{sid}',v.SID);
        v_kill:=REPLACE(v_kill,'{serial#}',v.serial#);
        EXECUTE IMMEDIATE v_kill;
    END LOOP;
END;
/

Seems like we are ready now to drop the user? Not so quick! While we were dropping existing sessions, the user might have opened more new sessions, so dropping a user may still fail with the same error:

ORA-01940: cannot drop a user that is currently connected

To work around this issue, we need to apply a simple trick: revoke a privilege to create a session before killing existing sessions! Here is a complete script (to be executed as SYS or SYSTEM):

REVOKE CONNECT FROM scott -- so new sessions will be blocked
/
--If CREATE SESSION privilege was granted to scott, we may want to revoke it as well
--Kill existing sessions:
DECLARE
    CURSOR c IS
    SELECT sid, serial#
    FROM v$session 
    where username='SCOTT';

    v_sql  VARCHAR2(200):='ALTER SYSTEM KILL SESSION ''{sid},{serial#}'' IMMEDIATE';
    v_kill VARCHAR2(200);
BEGIN
    FOR v IN c LOOP
        v_kill:=REPLACE(v_sql,'{sid}',v.SID);
        v_kill:=REPLACE(v_kill,'{serial#}',v.serial#);
        EXECUTE IMMEDIATE v_kill;
    END LOOP;
END;
/
--Drop the user
DROP USER SCOTT CASCADE
/

ORA-01578. Determine the name of the object with a corrupted data block.

If alert log file contains ORA-01578: ORACLE data block corrupted (file # x, block # xyz), you can determine the name of the object that has a corrupted data block:

SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = &fileid
  AND &blockid BETWEEN block_id AND block_id + blocks - 1

Week_Start and Week_End custom functions in Oracle, by Zahar Hilkevich

Frequently, there is a need to obtain a start and end dates of a week for a given date. The following SQL can be used to get the first day of the current week:

SELECT TRUNC(SYSDATE, 'DAY') week_start
FROM dual

Result:

WEEK_STAR
---------
25-AUG-13

Note, that TRUNC(SYSDATE, ‘IW’) will always give us Monday as ISO year (and IW format points to it) always starts on Monday, so we cannot generically use this format.

Getting a week’s end is even simpler:

SELECT TRUNC(SYSDATE, 'DAY') week_start,
       TRUNC(SYSDATE, 'DAY') + 6 week_end
FROM dual

Result:

WEEK_STAR WEEK_END
--------- ---------
25-AUG-13 31-AUG-13

or if we want to see the day of the week:

SELECT TO_CHAR(TRUNC(SYSDATE, 'DAY'), 'MM/DD/YY DAY') week_start,
       TO_CHAR(TRUNC(SYSDATE, 'DAY') + 6, 'MM/DD/YY DAY')  week_end
FROM dual

Result:

WEEK_START           WEEK_END
-------------------- ------------------
08/25/13 SUNDAY      08/31/13 SATURDAY

Now, let’s wrap it in PL/SQL functions:

CREATE OR REPLACE FUNCTION week_start(p_date DATE:=SYSDATE)
RETURN DATE
IS
BEGIN
    RETURN TRUNC(p_date, 'DAY');
END week_start;
/
CREATE OR REPLACE FUNCTION week_end(p_date DATE:=SYSDATE)
RETURN DATE
IS
BEGIN
    RETURN TRUNC(p_date, 'DAY')+6;
END week_end;
/
SELECT TO_CHAR(week_start, 'MM/DD/YY DAY') week_start,
       TO_CHAR(week_end, 'MM/DD/YY DAY')  week_end
FROM dual

Result:

WEEK_START           WEEK_END
-------------------- -----------------
08/25/13 SUNDAY      08/31/13 SATURDAY

Now, let’s make things more complicated. What if a client wants a week to start with Saturday and end on Friday? For that matter, we will introduce a new function parameter: p_shift – it will control the shift from Sunday (or whatever your local setting is):

CREATE OR REPLACE FUNCTION week_start(p_date DATE:=SYSDATE, 
                                      p_shift NUMBER:=0)
RETURN DATE
IS
BEGIN
    IF NOT ABS(p_shift) BETWEEN 0 AND 6 THEN
        RETURN NULL; --or raise exception
    END IF;
    RETURN TRUNC(p_date-p_shift, 'DAY')+p_shift;
END week_start;
/
CREATE OR REPLACE FUNCTION week_end(p_date DATE:=SYSDATE,
                                    p_shift NUMBER:=0)
RETURN DATE
IS
BEGIN
    IF NOT ABS(p_shift) BETWEEN 0 AND 6 THEN
        RETURN NULL; --or raise exception
    END IF;
    RETURN TRUNC(p_date-p_shift, 'DAY')+p_shift+6;
END week_end;
/
SELECT TO_CHAR(week_start(p_shift=>1), 'MM/DD/YY DAY') week_start,
       TO_CHAR(week_end(p_shift=>1), 'MM/DD/YY DAY')  week_end
FROM dual

Result:

WEEK_START           WEEK_END
-------------------- ----------------
08/26/13 MONDAY      09/01/13 SUNDAY

The above functions can be created separately or together. If we always create both of them, we should eliminate redundant code and leverage existing:

CREATE OR REPLACE FUNCTION week_end(p_date DATE:=SYSDATE,
                                    p_shift NUMBER:=0)
RETURN DATE
IS
BEGIN
    RETURN week_start(p_date, p_shift)+6;
END week_end;
SELECT TO_CHAR(week_start(p_shift=>-1), 'MM/DD/YY DAY') week_start,
       TO_CHAR(week_end(p_shift=>-1), 'MM/DD/YY DAY')  week_end
FROM dual

Result:

WEEK_START           WEEK_END
-------------------- ----------------
08/24/13 SATURDAY    08/30/13 FRIDAY

Helpful reference:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions230.htm

Q&A: Which Oracle user to use for RMAN backup?

Any Oracle user with SYSDBA role can run RMAN backup, so we suggest creating a dedicated user (rman) for that matter. Make sure that the number of entries in the password file leaves you room for adding one more user. If not, then use one of the existing sysdba users. For security reasons, it is slightly better to use a user with SYSDBA role who lacks “CREATE SESSION” privilege, so it cannot be used as a regular user.

conn sys AS SYSDBA
CREATE USER rman IDENTIFIED BY password;
GRANT SYSDBA TO rman;

To find existing users with SYSDBA role, see my recent post.

To find the maximum number of entries in the password file, use the formula:

Take the size of the password file (in bytes), subtract 1024 (header size) and divide by 128 (number of bytes per 1 entry)

Find Oracle users with sysdba role granted

--CONNECT AS A USER WHO HAS RIGHTS TO SELECT FROM V$PWFILE_USERS
SELECT *
from V$PWFILE_USERS

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

SQL> DESC V$PWFILE_USERS
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 USERNAME                                           VARCHAR2(30)
 SYSDBA                                             VARCHAR2(5)
 SYSOPER                                            VARCHAR2(5)
 SYSASM                                             VARCHAR2(5)

Random Sort Order in Oracle SQL, by Zahar Hilkevich

The easiest way to do random sorting in SQL is to use a function that returns different value every time it is invoked. In Oracle SQL, a function sys_guid() serves the purpose very well:

SELECT empno, ename, job, deptno
FROM emp
ORDER BY sys_guid()

sys_guid() is normally used to generate a GUID value in Oracle SQL.

Undocumented Oracle optimizer hint “MATERIALIZE”, by Zahar Hilkevich

Every time you use an in-line view or a WITH clause Oracle optimizer may (or may not) choose to use a temporary table based on the in-line view result set.

For example, for the following query Oracle optimizer does not use a temp table:

WITH b AS
(SELECT deptno, MAX(sal) max_sal
 FROM emp
 GROUP BY deptno
)
SELECT a.ename, a.sal, a.deptno
FROM emp a JOIN b ON 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 |
-----------------------------------------------------------------------------

To force Oracle optimizer using a temporary table instead of in-line view use materialize Oracle optimizer hint:

WITH b AS
(SELECT /*+materialize */ deptno, MAX(sal) max_sal
 FROM emp
 GROUP BY deptno
)
SELECT a.ename, a.sal, a.deptno
FROM emp a JOIN b ON a.deptno=b.deptno AND a.sal=b.max_sal

Execution Plan:

Plan hash value: 3092985208

----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |     3 |   117 |    10  (20)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6A06_F425182A |       |       |            |          |
|   3 |    HASH GROUP BY           |                             |     3 |    21 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL      | EMP                         |    14 |    98 |     3   (0)| 00:00:01 |
|*  5 |   HASH JOIN                |                             |     3 |   117 |     6  (17)| 00:00:01 |
|   6 |    VIEW                    |                             |     3 |    78 |     2   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6A06_F425182A |     3 |    21 |     2   (0)| 00:00:01 |
|   8 |    TABLE ACCESS FULL       | EMP                         |    14 |   182 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

P.S. If you like this tip, you can find many more in my book “Oracle SQL Tricks and Workarounds”

15 Workarounds for Getting Top Records

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.

Substitute a self outer join with Connect By, by Zahar Hilkevich

Self [outer] join is a very common and useful technique in Oracle SQL and in other flavors of SQL.

Let’s consider a trivial task of retrieving manager’s name and number next to each employee’s record (we are using scott schema):

SELECT a.ename, a.empno, a.job, a.deptno,
       b.ename AS manager,
       b.empno AS mgrno
FROM emp a LEFT JOIN emp b ON a.mgr=b.empno
ORDER BY 1

Result:

ENAME           EMPNO JOB           DEPTNO MANAGER         MGRNO
---------- ---------- --------- ---------- ---------- ----------
ADAMS            7876 CLERK             20 SCOTT            7788
ALLEN            7499 SALESMAN          30 BLAKE            7698
BLAKE            7698 MANAGER           30 KING             7839
CLARK            7782 MANAGER           10 KING             7839
FORD             7902 ANALYST           20 JONES            7566
JAMES            7900 CLERK             30 BLAKE            7698
JONES            7566 MANAGER           20 KING             7839
KING             7839 PRESIDENT         10
MARTIN           7654 SALESMAN          30 BLAKE            7698
MILLER           7934 CLERK             10 CLARK            7782
SCOTT            7788 ANALYST           20 JONES            7566
SMITH            7369 CLERK             20 FORD             7902
TURNER           7844 SALESMAN          30 BLAKE            7698
WARD             7521 SALESMAN          30 BLAKE            7698

Execution Plan
----------------------------------------------------------
Plan hash value: 2322654302

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   490 |     8  (25)| 00:00:01 |
|   1 |  SORT ORDER BY      |      |    14 |   490 |     8  (25)| 00:00:01 |
|*  2 |   HASH JOIN OUTER   |      |    14 |   490 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  |    14 |   350 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - access("A"."MGR"="B"."EMPNO"(+))

Note, that an outer join (left join) was used here to retrieve KING’s record as that employee is a president and does not have a manager.

The following workaround is based on the CONNECT BY clause without the use of START WITH as we need to retrieve all employee records:

SELECT ename, empno, job, deptno,
       MAX(PRIOR ename) AS manager,
       MAX(PRIOR empno) AS mgrno
FROM emp
WHERE LEVEL<=2
CONNECT BY mgr=PRIOR empno
GROUP BY ename, empno, job, deptno
ORDER BY 1

Result:

ENAME           EMPNO JOB           DEPTNO MANAGER         MGRNO
---------- ---------- --------- ---------- ---------- ----------
ADAMS            7876 CLERK             20 SCOTT            7788
ALLEN            7499 SALESMAN          30 BLAKE            7698
BLAKE            7698 MANAGER           30 KING             7839
CLARK            7782 MANAGER           10 KING             7839
FORD             7902 ANALYST           20 JONES            7566
JAMES            7900 CLERK             30 BLAKE            7698
JONES            7566 MANAGER           20 KING             7839
KING             7839 PRESIDENT         10
MARTIN           7654 SALESMAN          30 BLAKE            7698
MILLER           7934 CLERK             10 CLARK            7782
SCOTT            7788 ANALYST           20 JONES            7566
SMITH            7369 CLERK             20 FORD             7902
TURNER           7844 SALESMAN          30 BLAKE            7698
WARD             7521 SALESMAN          30 BLAKE            7698

Execution Plan
----------------------------------------------------------
Plan hash value: 2826654915

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |    14 |   350 |     4  (25)| 00:00:01 |
|   1 |  SORT GROUP BY                 |      |    14 |   350 |     4  (25)| 00:00:01 |
|*  2 |   FILTER                       |      |       |       |            |          |
|*  3 |    CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   4 |     TABLE ACCESS FULL          | EMP  |    14 |   350 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   2 - filter(LEVEL<=2)
   3 - access("MGR"=PRIOR "EMPNO")

This execution plan looks somewhat better than the previous one, mainly, because we only used a single copy of the emp table while the first query used two.

P.S. If you like this trick, you can find many more in my book “Oracle SQL Tricks and Workarounds”

Find a top record without using a subquery and in-line view/with clause, by Zahar Hilkevich

We will be finding a top paid employee from the scott’s emp table.

With a help of a subquery, the task becomes trivial:

SELECT ename, job, sal
FROM emp
WHERE sal=(SELECT MAX(sal) FROM emp)

Result:

ENAME      JOB              SAL
---------- --------- ----------
KING       PRESIDENT       5000

Alternatively, you can use an in-line view:

SELECT ename, job, sal
FROM (SELECT ename, job, sal, RANK()OVER(ORDER BY sal DESC) rk
      FROM emp)
WHERE rk=1

Result:

ENAME      JOB              SAL
---------- --------- ----------
KING       PRESIDENT       5000

The exercise is to achieve the same without a help of “another” query which is usually provided by a subquery, in-line view, or WITH clause:

SELECT a.ename, a.job, a.sal
FROM emp a, emp b
GROUP BY a.ename, a.job, a.sal
HAVING a.sal=MAX(b.sal)

Result:

ENAME      JOB              SAL
---------- --------- ----------
KING       PRESIDENT       5000

We have just used a Cartesian Product of two instances of the emp table!

You can read about 15 Workarounds for Getting Top Records in my new post.

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds”.