Oracle 12c: Scalar Subquery Unnesting and ROWNUM

After migration from 11.2.0.4. to 12.1.0.2 I discovered an interesting thing. I’ve read a lot about the new features and I was particularly interested in the Scalar Subquery Unnesting transformation. After a few tests, however, I found something that I hadn’t really expected 🙂

I don’t want to explain how this transformation works because these guys did it better than I would ever do…:
Jonathan Lewis
Tanel Poder

I only want to share with you my experiences:)

My database version:

SQL> SELECT banner FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

So, this is our basic query:

SQL> ;
  1  SELECT /*test1*/
  2         pp.employee_id,
  3         pp.first_name,
  4         pp.last_name,
  5         (SELECT aa.job_title FROM jobs aa WHERE aa.job_id = pp.job_id AND ROWNUM =1 ) job_title
  6    FROM employees pp
  7   WHERE pp.department_id IN (90,
  8*                             30)
SQL> /

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 JOB_TITLE
----------- -------------------- ------------------------- -----------------------------------
        114 Den                  Raphaely                  Purchasing Manager
        115 Alexander            Khoo                      Purchasing Clerk
        116 Shelli               Baida                     Purchasing Clerk
        117 Sigal                Tobias                    Purchasing Clerk
        118 Guy                  Himuro                    Purchasing Clerk
        119 Karen                Colmenares                Purchasing Clerk
        100 Steven               King                      President
        101 Neena                Kochhar                   Administration Vice President
        102 Lex                  De Haan                   Administration Vice President

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1199194469

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |     9 |   279 |     7   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |                   |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| JOBS              |     1 |    27 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | JOB_ID_PK         |     1 |       |     0   (0)| 00:00:01 |
|   4 |  INLIST ITERATOR             |                   |       |       |            |          |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     9 |   279 |     2   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |     9 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)
   3 - access("AA"."JOB_ID"=:B1)
   6 - access("PP"."DEPARTMENT_ID"=30 OR "PP"."DEPARTMENT_ID"=90)
********************
Subquery Unnest (SU)
********************
SU: bypassed: Scalar subquery may return more than one row.
SJC: Considering set-join conversion in query block SEL$1 (#0)
...
...
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "PP"."EMPLOYEE_ID" "EMPLOYEE_ID",
       "PP"."FIRST_NAME" "FIRST_NAME",
       "PP"."LAST_NAME" "LAST_NAME",
       (SELECT "AA"."JOB_TITLE" "JOB_TITLE"
          FROM "HR"."JOBS" "AA"
         WHERE "AA"."JOB_ID" = "PP"."JOB_ID"
           AND rownum = 1) "JOB_TITLE"
  FROM "HR"."EMPLOYEES" "PP"
 WHERE "PP"."DEPARTMENT_ID" = 90
    OR "PP"."DEPARTMENT_ID" = 30



As we can see in the Execution Plan and the 10053 trace file the transformation doesn’t work.

It’s really interesting in spite of the fact that we take the data from JOBS table via primary key and ROWNUM=1 and we get “Scalar subquery may return more than one row” 🙂

But when we make a little change… only add MAX() function to the select statement, we’ll see that the Scalar Subquery Unnesting transformation is done.

SQL> SELECT /*test2*/
  2         pp.employee_id,
  3         pp.first_name,
  4         pp.last_name,
  5         (SELECT max(aa.job_title) FROM jobs aa WHERE aa.job_id = pp.job_id AND ROWNUM =1 ) job_title
  6    FROM employees pp
  7   WHERE pp.department_id IN (90,
  8                              30);

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 JOB_TITLE
----------- -------------------- ------------------------- -----------------------------------
        100 Steven               King
        114 Den                  Raphaely
        102 Lex                  De Haan
        101 Neena                Kochhar
        119 Karen                Colmenares
        118 Guy                  Himuro
        117 Sigal                Tobias
        116 Shelli               Baida
        115 Alexander            Khoo

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2768938169

-----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                   |     9 |   513 |     4   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER                |                   |     9 |   513 |     4   (0)| 00:00:01 |
|   2 |   INLIST ITERATOR               |                   |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID  | EMPLOYEES         |     9 |   279 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN            | EMP_DEPARTMENT_IX |     9 |       |     1   (0)| 00:00:01 |
|   5 |   VIEW                          | VW_SSQ_1          |     1 |    26 |     2   (0)| 00:00:01 |
|   6 |    HASH GROUP BY                |                   |     1 |    27 |     2   (0)| 00:00:01 |
|*  7 |     COUNT STOPKEY               |                   |       |       |            |          |
|   8 |      TABLE ACCESS BY INDEX ROWID| JOBS              |     1 |    27 |     2   (0)| 00:00:01 |
|   9 |       INDEX FULL SCAN           | JOB_ID_PK         |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   1 - access("ITEM_1"(+)="PP"."JOB_ID")
   4 - access("PP"."DEPARTMENT_ID"=30 OR "PP"."DEPARTMENT_ID"=90)
   7 - filter(ROWNUM=1)
*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$1 (#1).
SU:   Unnesting  scalar subquery query block SEL$2 (#2)Registered qb: SEL$683B0107 0x21aa3770 (SUBQ INTO VIEW FOR COMPLEX UNNEST SEL$2)
...
...
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "PP"."EMPLOYEE_ID"             "EMPLOYEE_ID",
       "PP"."FIRST_NAME"              "FIRST_NAME",
       "PP"."LAST_NAME"               "LAST_NAME",
       "VW_SSQ_1"."MAX(AA.JOB_TITLE)" "JOB_TITLE"
  FROM (SELECT MAX("AA"."JOB_TITLE") "MAX(AA.JOB_TITLE)",
               "AA"."JOB_ID" "ITEM_1"
          FROM "HR"."JOBS" "AA"
         WHERE rownum = 1
         GROUP BY "AA"."JOB_ID") "VW_SSQ_1",
       "HR"."EMPLOYEES" "PP" 
 WHERE("PP"."DEPARTMENT_ID" = 30 OR "PP"."DEPARTMENT_ID" = 90) 
   AND "VW_SSQ_1"."ITEM_1"(+) = "PP"."JOB_ID"



Great, but one moment please….where is the data from the JOB_TITLE column ???

Yes, the select statement from the inner subquery returns null because condition ROWNUM = 1.
The query transformation moved the select statement from the projection list to the WHERE clause, added condition “VW_SSQ_1”.”ITEM_1″(+) = “PP”.”JOB_ID” but condition with ROWNUM=1 stayed in query. So, the query can return only one random row….

SELECT "PP"."EMPLOYEE_ID"             "EMPLOYEE_ID",
       "PP"."FIRST_NAME"              "FIRST_NAME",
       "PP"."LAST_NAME"               "LAST_NAME",
       "VW_SSQ_1"."MAX(AA.JOB_TITLE)" "JOB_TITLE"
  FROM (SELECT MAX("AA"."JOB_TITLE") "MAX(AA.JOB_TITLE)",
               "AA"."JOB_ID" "ITEM_1"
          FROM "HR"."JOBS" "AA"
         WHERE rownum = 1
         GROUP BY "AA"."JOB_ID") "VW_SSQ_1",
       "HR"."EMPLOYEES" "PP" 
 WHERE("PP"."DEPARTMENT_ID" = 30 OR "PP"."DEPARTMENT_ID" = 90) 
   AND "VW_SSQ_1"."ITEM_1"(+) = "PP"."JOB_ID"



When we turn off the “_optimizer_unnest_scalar_sq” parameter or use hint NO_UNNEST

ALTER SESSION SET "_optimizer_unnest_scalar_sq"=FALSE;

and we repet the query, we’ll see the data in theJOB_TITLE column, but we won’t see the transformation:

SQL> SELECT /*test2*/
  2         pp.employee_id,
  3         pp.first_name,
  4         pp.last_name,
  5         (SELECT max(aa.job_title) FROM jobs aa WHERE aa.job_id = pp.job_id AND ROWNUM =1 ) job_title
  6    FROM employees pp
  7   WHERE pp.department_id IN (90,
  8                              30);

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 JOB_TITLE
----------- -------------------- ------------------------- -----------------------------------
        114 Den                  Raphaely                  Purchasing Manager
        115 Alexander            Khoo                      Purchasing Clerk
        116 Shelli               Baida                     Purchasing Clerk
        117 Sigal                Tobias                    Purchasing Clerk
        118 Guy                  Himuro                    Purchasing Clerk
        119 Karen                Colmenares                Purchasing Clerk
        100 Steven               King                      President
        101 Neena                Kochhar                   Administration Vice President
        102 Lex                  De Haan                   Administration Vice President

9 rows selected.
********************
Subquery Unnest (SU)
********************
SU: Scalar subquery unnesting disabled by parameter.
SJC: Considering set-join conversion in query block SEL$1 (#0)
...
...
...
FINAL query after transformations:******* UNPARSED QUERY IS *******
SELECT "PP"."EMPLOYEE_ID" "EMPLOYEE_ID",
       "PP"."FIRST_NAME" "FIRST_NAME",
       "PP"."LAST_NAME" "LAST_NAME",
       (SELECT MAX("AA"."JOB_TITLE") "MAX(AA.JOB_TITLE)"
          FROM "HR"."JOBS" "AA"
         WHERE "AA"."JOB_ID" = "PP"."JOB_ID"
           AND rownum = 1) "JOB_TITLE"
  FROM "HR"."EMPLOYEES" "PP"
 WHERE "PP"."DEPARTMENT_ID" = 90
    OR "PP"."DEPARTMENT_ID" = 30

When we remove the condition with ROWNUM and turn on the parameter, everything is fine:

ALTER SESSION SET "_optimizer_unnest_scalar_sq"=TRUE;
SQL> SELECT /*test3*/
  2         pp.employee_id,
  3         pp.first_name,
  4         pp.last_name,
  5         (SELECT max(aa.job_title) FROM jobs aa WHERE aa.job_id = pp.job_id ) job_title
  6    FROM employees pp
  7   WHERE pp.department_id IN (90,
  8                              30);

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 JOB_TITLE
----------- -------------------- ------------------------- -----------------------------------
        119 Karen                Colmenares                Purchasing Clerk
        118 Guy                  Himuro                    Purchasing Clerk
        117 Sigal                Tobias                    Purchasing Clerk
        116 Shelli               Baida                     Purchasing Clerk
        115 Alexander            Khoo                      Purchasing Clerk
        102 Lex                  De Haan                   Administration Vice President
        101 Neena                Kochhar                   Administration Vice President
        100 Steven               King                      President
        114 Den                  Raphaely                  Purchasing Manager

9 rows selected.
*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$1 (#1).
SU:   Unnesting  scalar subquery query block SEL$2 (#2)Registered qb: SEL$683B0107 0x21aa3770 (SUBQ INTO VIEW FOR COMPLEX UNNEST SEL$2)
...
...
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "PP"."EMPLOYEE_ID"             "EMPLOYEE_ID",
       "PP"."FIRST_NAME"              "FIRST_NAME",
       "PP"."LAST_NAME"               "LAST_NAME",
       "VW_SSQ_1"."MAX(AA.JOB_TITLE)" "JOB_TITLE"
  FROM (SELECT MAX("AA"."JOB_TITLE") "MAX(AA.JOB_TITLE)",
               "AA"."JOB_ID" "ITEM_1"
          FROM "HR"."JOBS" "AA"
         GROUP BY "AA"."JOB_ID") "VW_SSQ_1",
       "HR"."EMPLOYEES" "PP"
 WHERE ("PP"."DEPARTMENT_ID" = 30 OR "PP"."DEPARTMENT_ID" = 90)
   AND "VW_SSQ_1"."ITEM_1"(+) = "PP"."JOB_ID"





Look out for this feature and use it carefully. And good pice of good advice: review your queries BEFORE migration. 🙂
Hope it will help you avoid a few problems…

Update 2017-09-18:
The problem has been solved.
Oracle 12.2.0.1.0 the 10053 trace file :

...
SU: Scalar subquery contains rownum.
...
********************
Subquery Unnest (SU)
********************
SU: Scalar subquery contains rownum.
SJC: Considering set-join conversion in query block SEL$1 (#0)

Leave a Reply

Be the First to Comment!

avatar
wpDiscuz