Home > Oracle, SQL > SQL Curiosities: Disconnected EXPLAIN PLAN

SQL Curiosities: Disconnected EXPLAIN PLAN


Filed under SQL curiosities. The Oracle documentation incorrectly states that “The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right. The next step is the parent of that line. If two lines are indented equally, then the top line is normally executed first.” The plan below is a counter-example. There are five tables involved: Employees, Departments, Locations, Countries, and Regions. Exactly one row is retrieved from each table as evidenced by the INDEX UNIQUE SCAN operations.

----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                 |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID     | REGIONS         |     1 |    14 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN              | REG_ID_PK       |     1 |       |     0   (0)|          |
|*  3 |    INDEX UNIQUE SCAN             | COUNTRY_C_ID_PK |     1 |     6 |     0   (0)|          |
|   4 |     TABLE ACCESS BY INDEX ROWID  | LOCATIONS       |     1 |     6 |     1   (0)| 00:00:01 |
|*  5 |      INDEX UNIQUE SCAN           | LOC_ID_PK       |     1 |       |     0   (0)|          |
|   6 |       TABLE ACCESS BY INDEX ROWID| DEPARTMENTS     |     1 |     7 |     1   (0)| 00:00:01 |
|*  7 |        INDEX UNIQUE SCAN         | DEPT_ID_PK      |     1 |       |     0   (0)|          |
|   8 |  TABLE ACCESS BY INDEX ROWID     | EMPLOYEES       |     1 |    22 |     1   (0)| 00:00:01 |
|*  9 |   INDEX UNIQUE SCAN              | EMP_EMP_ID_PK   |     1 |       |     0   (0)|          |
----------------------------------------------------------------------------------------------------

If you believed the Oracle documentation, you would conclude that the lines are executed in the sequence 7, 6, 5, 4, 3, 2, 1, 9, 8, 0. But, in reality, they are executed in the sequence 9, 8, 7, 6, 5, 4, 3, 2, 1, 0. The plan is quite a curiosity because the output of lines 1 and 8 are not connected by a join method.

What sort of query could produce the above plan? Click here for the answer.

Categories: Oracle, SQL
  1. December 28, 2010 at 10:25 am

    I presume this is related to the odd display sometimes of filter operations?

    The predicates section of the execution plan would confirm.

    If so, the relevant lines in the plan are effectively suppressed and also mentioned by Jonathan Lewis here:
    http://jonathanlewis.wordpress.com/2010/08/31/filter-bug/

  2. Iggy Fernandez
    December 28, 2010 at 11:13 am

    Hi, Dom,

    The seemingly disconnected EXPLAIN PLAN resulted from a scalar subquery in the SELECT list.

    Microsoft Windows XP [Version 5.1.2600]
    (C) Copyright 1985-2001 Microsoft Corp.
    
    C:\Documents and Settings\Administrator>sqlplus hr
    
    SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 28 10:38:58 2010
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    Enter password:
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> SET linesize 200
    SQL> SET pagesize 100
    SQL> SET tab off
    SQL> SET trimspool on
    SQL>
    SQL> ALTER SESSION SET statistics_level = all;
    
    Session altered.
    
    SQL>
    SQL> -- Scalar subquery in the SELECT list
    SQL>
    SQL> SELECT
      2    employee_id,
      3    first_name,
      4    last_name,
      5    (
      6      SELECT region_name
      7      FROM regions
      8      WHERE region_id =
      9      (
     10        SELECT region_id
     11        FROM countries
     12        WHERE country_id =
     13        (
     14          SELECT country_id
     15          FROM locations
     16          WHERE location_id =
     17          (
     18            SELECT location_id
     19            FROM departments
     20            WHERE department_id = e.department_id
     21          )
     22        )
     23      )
     24    ) AS region_name
     25  FROM employees e
     26  WHERE employee_id = 100;
    
    EMPLOYEE_ID FIRST_NAME           LAST_NAME                 REGION_NAME
    ----------- -------------------- ------------------------- -------------------------
            100 Steven               King                      Americas
    
    SQL>
    SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'ADVANCED IOSTATS LAST +PEEKED_BINDS'));
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    SQL_ID  4mjzjvjnhxphn, child number 0
    -------------------------------------
    SELECT   employee_id,   first_name,   last_name,   (     SELECT
    region_name     FROM regions     WHERE region_id =     (       SELECT
    region_id       FROM countries       WHERE country_id =       (
    SELECT country_id         FROM locations         WHERE location_id =
         (           SELECT location_id           FROM departments
     WHERE department_id = e.department_id         )       )     )   ) AS
    region_name FROM employees e WHERE employee_id = 100
    
    Plan hash value: 1669217690
    
    ----------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                        | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
    ----------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                 |                 |      1 |        |       |     1 (100)|          |      1 |00:00:00.01 |       2 |
    |   1 |  TABLE ACCESS BY INDEX ROWID     | REGIONS         |      1 |      1 |    14 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |
    |*  2 |   INDEX UNIQUE SCAN              | REG_ID_PK       |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       6 |
    |*  3 |    INDEX UNIQUE SCAN             | COUNTRY_C_ID_PK |      1 |      1 |     6 |     0   (0)|          |      1 |00:00:00.01 |       5 |
    |   4 |     TABLE ACCESS BY INDEX ROWID  | LOCATIONS       |      1 |      1 |     6 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |
    |*  5 |      INDEX UNIQUE SCAN           | LOC_ID_PK       |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       3 |
    |   6 |       TABLE ACCESS BY INDEX ROWID| DEPARTMENTS     |      1 |      1 |     7 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
    |*  7 |        INDEX UNIQUE SCAN         | DEPT_ID_PK      |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
    |   8 |  TABLE ACCESS BY INDEX ROWID     | EMPLOYEES       |      1 |      1 |    22 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
    |*  9 |   INDEX UNIQUE SCAN              | EMP_EMP_ID_PK   |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
    ----------------------------------------------------------------------------------------------------------------------------------------------
    
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
    
       1 - SEL$2 / REGIONS@SEL$2
       2 - SEL$2 / REGIONS@SEL$2
       3 - SEL$3 / COUNTRIES@SEL$3
       4 - SEL$4 / LOCATIONS@SEL$4
       5 - SEL$4 / LOCATIONS@SEL$4
       6 - SEL$5 / DEPARTMENTS@SEL$5
       7 - SEL$5 / DEPARTMENTS@SEL$5
       8 - SEL$1 / E@SEL$1
       9 - SEL$1 / E@SEL$1
    
    Outline Data
    -------------
    
      /*+
          BEGIN_OUTLINE_DATA
          IGNORE_OPTIM_EMBEDDED_HINTS
          OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
          DB_VERSION('11.2.0.1')
          ALL_ROWS
          OUTLINE_LEAF(@"SEL$5")
          OUTLINE_LEAF(@"SEL$4")
          OUTLINE_LEAF(@"SEL$3")
          OUTLINE_LEAF(@"SEL$2")
          OUTLINE_LEAF(@"SEL$1")
          INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMPLOYEES"."EMPLOYEE_ID"))
          INDEX_RS_ASC(@"SEL$2" "REGIONS"@"SEL$2" ("REGIONS"."REGION_ID"))
          PUSH_SUBQ(@"SEL$3")
          INDEX(@"SEL$3" "COUNTRIES"@"SEL$3" ("COUNTRIES"."COUNTRY_ID"))
          PUSH_SUBQ(@"SEL$4")
          INDEX_RS_ASC(@"SEL$4" "LOCATIONS"@"SEL$4" ("LOCATIONS"."LOCATION_ID"))
          PUSH_SUBQ(@"SEL$5")
          INDEX_RS_ASC(@"SEL$5" "DEPARTMENTS"@"SEL$5" ("DEPARTMENTS"."DEPARTMENT_ID"))
          END_OUTLINE_DATA
      */
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("REGION_ID"=)
       3 - access("COUNTRY_ID"=)
       5 - access("LOCATION_ID"=)
       7 - access("DEPARTMENT_ID"=:B1)
       9 - access("EMPLOYEE_ID"=100)
    
    Column Projection Information (identified by operation id):
    -----------------------------------------------------------
    
       1 - "REGION_NAME"[VARCHAR2,25]
       2 - "REGIONS".ROWID[ROWID,10]
       3 - "REGION_ID"[NUMBER,22]
       4 - "COUNTRY_ID"[CHARACTER,2]
       5 - "LOCATIONS".ROWID[ROWID,10]
       6 - "LOCATION_ID"[NUMBER,22]
       7 - "DEPARTMENTS".ROWID[ROWID,10]
       8 - "EMPLOYEE_ID"[NUMBER,22], "FIRST_NAME"[VARCHAR2,20], "LAST_NAME"[VARCHAR2,25], "E"."DEPARTMENT_ID"[NUMBER,22]
       9 - "SYS_ALIAS_4".ROWID[ROWID,10], "EMPLOYEE_ID"[NUMBER,22]
    
    
    87 rows selected.
    
    SQL>
    SQL> SET long 100000
    SQL> SET pagesize 0
    SQL> COLUMN DDL format a132
    SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);
    
    PL/SQL procedure successfully completed.
    
    SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
    
    PL/SQL procedure successfully completed.
    
    SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',false);
    
    PL/SQL procedure successfully completed.
    
    SQL>
    SQL> SELECT dbms_metadata.get_ddl('TABLE', table_name) AS ddl
      2  FROM user_tables
      3  WHERE table_name IN (
      4    'EMPLOYEES',
      5    'DEPARTMENTS',
      6    'LOCATIONS',
      7    'COUNTRIES',
      8    'REGIONS'
      9  )
     10  ORDER BY table_name;
    
      CREATE TABLE "HR"."COUNTRIES"
       (    "COUNTRY_ID" CHAR(2) CONSTRAINT "COUNTRY_ID_NN" NOT NULL ENABLE,
            "COUNTRY_NAME" VARCHAR2(40),
            "REGION_ID" NUMBER,
             CONSTRAINT "COUNTRY_C_ID_PK" PRIMARY KEY ("COUNTRY_ID") ENABLE,
             CONSTRAINT "COUNTR_REG_FK" FOREIGN KEY ("REGION_ID")
              REFERENCES "HR"."REGIONS" ("REGION_ID") ENABLE
       ) ORGANIZATION INDEX NOCOMPRESS
    
    
    
      CREATE TABLE "HR"."DEPARTMENTS"
       (    "DEPARTMENT_ID" NUMBER(4,0),
            "DEPARTMENT_NAME" VARCHAR2(30) CONSTRAINT "DEPT_NAME_NN" NOT NULL ENABLE,
            "MANAGER_ID" NUMBER(6,0),
            "LOCATION_ID" NUMBER(4,0),
             CONSTRAINT "DEPT_ID_PK" PRIMARY KEY ("DEPARTMENT_ID") ENABLE,
             CONSTRAINT "DEPT_MGR_FK" FOREIGN KEY ("MANAGER_ID")
              REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE,
             CONSTRAINT "DEPT_LOC_FK" FOREIGN KEY ("LOCATION_ID")
              REFERENCES "HR"."LOCATIONS" ("LOCATION_ID") ENABLE
       )
    
    
    
      CREATE TABLE "HR"."EMPLOYEES"
       (    "EMPLOYEE_ID" NUMBER(6,0),
            "FIRST_NAME" VARCHAR2(20),
            "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
            "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,
            "PHONE_NUMBER" VARCHAR2(20),
            "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
            "JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,
            "SALARY" NUMBER(8,2),
            "COMMISSION_PCT" NUMBER(2,2),
            "MANAGER_ID" NUMBER(6,0),
            "DEPARTMENT_ID" NUMBER(4,0),
             CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE,
             CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL") ENABLE,
             CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID") ENABLE,
             CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")
              REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE,
             CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID")
              REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE,
             CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
              REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE
       )
    
    
    
      CREATE TABLE "HR"."LOCATIONS"
       (    "LOCATION_ID" NUMBER(4,0),
            "STREET_ADDRESS" VARCHAR2(40),
            "POSTAL_CODE" VARCHAR2(12),
            "CITY" VARCHAR2(30) CONSTRAINT "LOC_CITY_NN" NOT NULL ENABLE,
            "STATE_PROVINCE" VARCHAR2(25),
            "COUNTRY_ID" CHAR(2),
             CONSTRAINT "LOC_ID_PK" PRIMARY KEY ("LOCATION_ID") ENABLE,
             CONSTRAINT "LOC_C_ID_FK" FOREIGN KEY ("COUNTRY_ID")
              REFERENCES "HR"."COUNTRIES" ("COUNTRY_ID") ENABLE
       )
    
    
    
      CREATE TABLE "HR"."REGIONS"
       (    "REGION_ID" NUMBER CONSTRAINT "REGION_ID_NN" NOT NULL ENABLE,
            "REGION_NAME" VARCHAR2(25),
             CONSTRAINT "REG_ID_PK" PRIMARY KEY ("REGION_ID") ENABLE
       )
    
    
    
    SQL>
    SQL> SELECT dbms_metadata.get_ddl('INDEX', index_name) AS ddl
      2  FROM user_indexes
      3  WHERE table_name IN (
      4    'EMPLOYEES',
      5    'DEPARTMENTS',
      6    'LOCATIONS',
      7    'COUNTRIES',
      8    'REGIONS'
      9  )
     10  ORDER BY
     11    table_name,
     12    index_name;
    
      CREATE UNIQUE INDEX "HR"."COUNTRY_C_ID_PK" ON "HR"."COUNTRIES" ("COUNTRY_ID")
    
    
    
    
      CREATE UNIQUE INDEX "HR"."DEPT_DEP_NAME_IX" ON "HR"."DEPARTMENTS" ("DEPARTMENT_NAME")
    
    
    
    
      CREATE UNIQUE INDEX "HR"."DEPT_ID_PK" ON "HR"."DEPARTMENTS" ("DEPARTMENT_ID")
    
    
    
    
      CREATE INDEX "HR"."DEPT_LOCATION_IX" ON "HR"."DEPARTMENTS" ("LOCATION_ID")
    
    
    
    
      CREATE INDEX "HR"."EMP_DEPARTMENT_IX" ON "HR"."EMPLOYEES" ("DEPARTMENT_ID")
    
    
    
    
      CREATE UNIQUE INDEX "HR"."EMP_EMAIL_UK" ON "HR"."EMPLOYEES" ("EMAIL")
    
    
    
    
      CREATE UNIQUE INDEX "HR"."EMP_EMP_ID_PK" ON "HR"."EMPLOYEES" ("EMPLOYEE_ID")
    
    
    
    
      CREATE INDEX "HR"."EMP_JOB_IX" ON "HR"."EMPLOYEES" ("JOB_ID")
    
    
    
    
      CREATE INDEX "HR"."EMP_MANAGER_IX" ON "HR"."EMPLOYEES" ("MANAGER_ID")
    
    
    
    
      CREATE INDEX "HR"."EMP_NAME_IX" ON "HR"."EMPLOYEES" ("LAST_NAME", "FIRST_NAME")
    
    
    
    
      CREATE INDEX "HR"."LOC_CITY_IX" ON "HR"."LOCATIONS" ("CITY")
    
    
    
    
      CREATE INDEX "HR"."LOC_COUNTRY_IX" ON "HR"."LOCATIONS" ("COUNTRY_ID")
    
    
    
    
      CREATE UNIQUE INDEX "HR"."LOC_ID_PK" ON "HR"."LOCATIONS" ("LOCATION_ID")
    
    
    
    
      CREATE INDEX "HR"."LOC_STATE_PROVINCE_IX" ON "HR"."LOCATIONS" ("STATE_PROVINCE")
    
    
    
    
      CREATE UNIQUE INDEX "HR"."REG_ID_PK" ON "HR"."REGIONS" ("REGION_ID")
    
    
    
    
    15 rows selected.
    
    SQL>
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    C:\Documents and Settings\Administrator>
    

    Cheers

  3. Simpson
    December 29, 2010 at 12:10 am

    Hi Iggy,

    How do you know that the execution sequence is: 9, 8, 7, 6, 5, 4, 3, 2, 1, 0 ???

    I drew the graph of the above execution plan and feel like the execution sequence is as mentioned in the doc.

    Your help is appreciated. Can you point me to a link/doc where I can learn more on execution sequence. Sorry for my ignorance.

    Thanks

  4. Bernard Polarski
    December 29, 2010 at 12:55 am

    Take a 10046 and see which table is fetch first. This type of plan are known as bushy plan. Bushy plan starts with the first table without a child and way of processing impacts all cumulative stats in the plan.

  5. December 29, 2010 at 3:21 am

    Ok – it’s a scalar subquery. So part of this is just standard non-transformed scalar subquery reporting in execution plans, isn’t it?.

    Using your example consider a silly statement like this:
    (not sure if the formatting will be preserved)

    explain plan for
    select e.department_id
    ,      (select department_id from departments d where d.department_id = e.department_id) did
    ,      (select department_id from departments d where d.department_id = e.department_id) did2
    ,      (select department_id from departments d where d.department_id = e.department_id) did3
    ,      (select department_id from departments d where d.department_id = e.department_id) did4
    from   employees e;
    
    select * from table(dbms_xplan.display);
    --------------------------------------------------------------------------------
    | Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |            |     1 |    13 |     2   (0)| 00:00:01 |
    |*  1 |  INDEX UNIQUE SCAN| DEPT_ID_PK |     1 |    13 |     1   (0)| 00:00:01 |
    |*  2 |  INDEX UNIQUE SCAN| DEPT_ID_PK |     1 |    13 |     1   (0)| 00:00:01 |
    |*  3 |  INDEX UNIQUE SCAN| DEPT_ID_PK |     1 |    13 |     1   (0)| 00:00:01 |
    |*  4 |  INDEX UNIQUE SCAN| DEPT_ID_PK |     1 |    13 |     1   (0)| 00:00:01 |
    |   5 |  TABLE ACCESS FULL| EMPLOYEES  |     1 |    13 |     2   (0)| 00:00:01 |
    --------------------------------------------------------------------------------
    
    
  6. December 29, 2010 at 3:30 am

    Sorry another comment – posted previous one prematurely.

    Scalar Subqueries are one of the exceptions to the general rules.

    And the documentation certainly isn’t comprehensive in that regard.

  7. Simpson
    December 29, 2010 at 5:40 am

    Iggy, did you run the 10046 trace to confirm a different execution sequence ???

  8. Iggy Fernandez
    December 29, 2010 at 10:20 am

    Simpson :

    Hi Iggy,

    How do you know that the execution sequence is: 9, 8, 7, 6, 5, 4, 3, 2, 1, 0 ???

    I drew the graph of the above execution plan and feel like the execution sequence is as mentioned in the doc.

    Your help is appreciated. Can you point me to a link/doc where I can learn more on execution sequence. Sorry for my ignorance.

    Thanks

    Hi, Simpson,

    Intuitively, Oracle must first retrieve a unique record from the Employees table. Let’s verify this using a 10046 trace.

    Microsoft Windows XP [Version 5.1.2600]
    (C) Copyright 1985-2001 Microsoft Corp.
    
    C:\Documents and Settings\Administrator>sqlplus "/ as sysdba"
    
    SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 29 08:58:15 2010
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> SET linesize 200
    SQL> SET pagesize 100
    SQL> SET tab off
    SQL> SET trimspool on
    SQL>
    SQL> ALTER SESSION SET statistics_level = all;
    
    Session altered.
    
    SQL> ALTER SESSION SET current_schema = HR;
    
    Session altered.
    
    SQL>
    SQL> COLUMN object_name FORMAT a30
    SQL>
    SQL> SELECT
      2    object_id,
      3    object_type,
      4    object_name
      5  FROM
      6    dba_objects
      7  WHERE owner = 'HR'
      8  AND object_type = 'TABLE'
      9  AND object_name IN (
     10    'EMPLOYEES',
     11    'DEPARTMENTS',
     12    'LOCATIONS',
     13    'COUNTRIES',
     14    'REGIONS'
     15  )
     16  UNION
     17  SELECT
     18    object_id,
     19    object_type,
     20    object_name
     21  FROM
     22    dba_objects
     23  WHERE owner = 'HR'
     24  AND object_type = 'INDEX'
     25  AND object_name IN (
     26    SELECT index_name
     27    FROM dba_indexes
     28    WHERE table_owner = 'HR'
     29    AND table_name IN (
     30      'EMPLOYEES',
     31      'DEPARTMENTS',
     32      'LOCATIONS',
     33      'COUNTRIES',
     34      'REGIONS'
     35    )
     36  )
     37  ORDER BY
     38    object_id;
    
     OBJECT_ID OBJECT_TYPE         OBJECT_NAME
    ---------- ------------------- ------------------------------
         73934 TABLE               REGIONS
         73935 INDEX               REG_ID_PK
         73936 TABLE               COUNTRIES
         73937 INDEX               COUNTRY_C_ID_PK
         73938 TABLE               LOCATIONS
         73939 INDEX               LOC_ID_PK
         73940 INDEX               LOC_CITY_IX
         73941 INDEX               LOC_STATE_PROVINCE_IX
         73942 INDEX               LOC_COUNTRY_IX
         73943 TABLE               DEPARTMENTS
         73944 INDEX               DEPT_ID_PK
         73945 INDEX               DEPT_LOCATION_IX
         73948 TABLE               EMPLOYEES
         73949 INDEX               EMP_EMAIL_UK
         73950 INDEX               EMP_EMP_ID_PK
         73951 INDEX               EMP_DEPARTMENT_IX
         73952 INDEX               EMP_JOB_IX
         73953 INDEX               EMP_MANAGER_IX
         73954 INDEX               EMP_NAME_IX
         75127 INDEX               DEPT_DEP_NAME_IX
    
    20 rows selected.
    
    SQL>
    SQL> -- Scalar subquery in the SELECT list
    SQL>
    SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
    
    System altered.
    
    SQL> ALTER SESSION SET tracefile_identifier = 'SCALAR_SUBQUERY';
    
    Session altered.
    
    SQL> EXEC dbms_monitor.session_trace_enable(NULL, NULL, TRUE, TRUE);
    
    PL/SQL procedure successfully completed.
    
    SQL>
    SQL> SELECT
      2    employee_id,
      3    first_name,
      4    last_name,
      5    (
      6      SELECT region_name
      7      FROM regions
      8      WHERE region_id =
      9      (
     10        SELECT region_id
     11        FROM countries
     12        WHERE country_id =
     13        (
     14          SELECT country_id
     15          FROM locations
     16          WHERE location_id =
     17          (
     18            SELECT location_id
     19            FROM departments
     20            WHERE department_id = e.department_id
     21          )
     22        )
     23      )
     24    ) AS region_name
     25  FROM employees e
     26  WHERE employee_id = 100;
    
    EMPLOYEE_ID FIRST_NAME           LAST_NAME                 REGION_NAME
    ----------- -------------------- ------------------------- -------------------------
            100 Steven               King                      Americas
    
    SQL>
    SQL> EXEC dbms_monitor.session_trace_disable(NULL, NULL);
    
    PL/SQL procedure successfully completed.
    
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    C:\Documents and Settings\Administrator>
    

    Here is the trace file that was produced. As expected, the first object that is touched is the index on Employees with OBJECT_ID = 73950.

    Trace file c:\app3\ifernandez\diag\rdbms\tst2\tst2\trace\tst2_ora_3148_SCALAR_SUBQUERY.trc
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Windows XP Version V5.1 Service Pack 3 
    CPU                 : 2 - type 586, 2 Physical Cores
    Process Affinity    : 0x0x00000000
    Memory (Avail/Total): Ph:730M/2038M, Ph+PgF:2437M/3933M, VA:1523M/2047M 
    Instance name: tst2
    Redo thread mounted by this instance: 1
    Oracle process number: 24
    Windows thread id: 3148, image: ORACLE.EXE (SHAD)
    
    
    *** 2010-12-29 08:58:36.296
    *** SESSION ID:(19.29) 2010-12-29 08:58:36.296
    *** CLIENT ID:() 2010-12-29 08:58:36.296
    *** SERVICE NAME:(SYS$USERS) 2010-12-29 08:58:36.296
    *** MODULE NAME:(sqlplus.exe) 2010-12-29 08:58:36.296
    *** ACTION NAME:() 2010-12-29 08:58:36.296
     
    =====================
    PARSING IN CURSOR #1 len=71 dep=0 uid=85 oct=47 lid=0 tim=482256220086 hv=4186219046 ad='1a737290' sqlid='470gu5vws98j6'
    BEGIN dbms_monitor.session_trace_enable(NULL, NULL, TRUE, TRUE); END;
    END OF STMT
    EXEC #1:c=0,e=2176,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=482256220081
    WAIT #1: nam='SQL*Net message to client' ela= 6 driver id=1111838976 #bytes=1 p3=0 obj#=14 tim=482256233248
    WAIT #1: nam='SQL*Net message from client' ela= 102687 driver id=1111838976 #bytes=1 p3=0 obj#=14 tim=482256335985
    CLOSE #1:c=0,e=43,dep=0,type=1,tim=482256336206
    =====================
    PARSING IN CURSOR #1 len=472 dep=0 uid=85 oct=3 lid=0 tim=482256336346 hv=1762579988 ad='1a773e2c' sqlid='4mjzjvjnhxphn'
    SELECT
      employee_id,
      first_name,
      last_name,
      (
        SELECT region_name
        FROM regions
        WHERE region_id =
        (
          SELECT region_id
          FROM countries
          WHERE country_id =
          (
            SELECT country_id
            FROM locations
            WHERE location_id =
            (
              SELECT location_id
              FROM departments
              WHERE department_id = e.department_id
            )
          )
        )
      ) AS region_name
    FROM employees e
    WHERE employee_id = 100
    END OF STMT
    PARSE #1:c=0,e=69,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1669217690,tim=482256336342
    EXEC #1:c=0,e=66,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1669217690,tim=482256336774
    WAIT #1: nam='SQL*Net message to client' ela= 4 driver id=1111838976 #bytes=1 p3=0 obj#=14 tim=482256336834
    WAIT #1: nam='Disk file operations I/O' ela= 968 FileOperation=2 fileno=5 filetype=2 obj#=73950 tim=482256337953
    WAIT #1: nam='db file sequential read' ela= 14769 file#=5 block#=219 blocks=1 obj#=73950 tim=482256352769
    WAIT #1: nam='db file sequential read' ela= 6024 file#=5 block#=207 blocks=1 obj#=73948 tim=482256358967
    WAIT #1: nam='db file sequential read' ela= 2082 file#=5 block#=179 blocks=1 obj#=73944 tim=482256361261
    WAIT #1: nam='db file sequential read' ela= 7545 file#=5 block#=175 blocks=1 obj#=73943 tim=482256368873
    WAIT #1: nam='db file sequential read' ela= 558 file#=5 block#=163 blocks=1 obj#=73939 tim=482256369575
    WAIT #1: nam='db file sequential read' ela= 651 file#=5 block#=159 blocks=1 obj#=73938 tim=482256370293
    WAIT #1: nam='db file sequential read' ela= 6225 file#=5 block#=131 blocks=1 obj#=73937 tim=482256376748
    WAIT #1: nam='db file sequential read' ela= 2492 file#=5 block#=147 blocks=1 obj#=73935 tim=482256379474
    WAIT #1: nam='db file sequential read' ela= 791 file#=5 block#=143 blocks=1 obj#=73934 tim=482256380329
    FETCH #1:c=0,e=43479,p=9,cr=9,cu=0,mis=0,r=1,dep=0,og=1,plh=1669217690,tim=482256380407
    STAT #1 id=1 cnt=1 pid=0 pos=1 obj=73934 op='TABLE ACCESS BY INDEX ROWID REGIONS (cr=7 pr=7 pw=0 time=0 us cost=1 size=14 card=1)'
    STAT #1 id=2 cnt=1 pid=1 pos=1 obj=73935 op='INDEX UNIQUE SCAN REG_ID_PK (cr=6 pr=6 pw=0 time=0 us cost=0 size=0 card=1)'
    STAT #1 id=3 cnt=1 pid=2 pos=1 obj=73937 op='INDEX UNIQUE SCAN COUNTRY_C_ID_PK (cr=5 pr=5 pw=0 time=0 us cost=0 size=6 card=1)'
    STAT #1 id=4 cnt=1 pid=3 pos=1 obj=73938 op='TABLE ACCESS BY INDEX ROWID LOCATIONS (cr=4 pr=4 pw=0 time=0 us cost=1 size=6 card=1)'
    STAT #1 id=5 cnt=1 pid=4 pos=1 obj=73939 op='INDEX UNIQUE SCAN LOC_ID_PK (cr=3 pr=3 pw=0 time=0 us cost=0 size=0 card=1)'
    STAT #1 id=6 cnt=1 pid=5 pos=1 obj=73943 op='TABLE ACCESS BY INDEX ROWID DEPARTMENTS (cr=2 pr=2 pw=0 time=0 us cost=1 size=7 card=1)'
    STAT #1 id=7 cnt=1 pid=6 pos=1 obj=73944 op='INDEX UNIQUE SCAN DEPT_ID_PK (cr=1 pr=1 pw=0 time=0 us cost=0 size=0 card=1)'
    STAT #1 id=8 cnt=1 pid=0 pos=2 obj=73948 op='TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=2 pr=2 pw=0 time=0 us cost=1 size=22 card=1)'
    STAT #1 id=9 cnt=1 pid=8 pos=1 obj=73950 op='INDEX UNIQUE SCAN EMP_EMP_ID_PK (cr=1 pr=1 pw=0 time=0 us cost=0 size=0 card=1)'
    WAIT #1: nam='SQL*Net message from client' ela= 283 driver id=1111838976 #bytes=1 p3=0 obj#=73934 tim=482256381273
    FETCH #1:c=0,e=5,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1669217690,tim=482256381336
    WAIT #1: nam='SQL*Net message to client' ela= 4 driver id=1111838976 #bytes=1 p3=0 obj#=73934 tim=482256381383
    
    *** 2010-12-29 08:58:37.765
    WAIT #1: nam='SQL*Net message from client' ela= 1316179 driver id=1111838976 #bytes=1 p3=0 obj#=73934 tim=482257697597
    CLOSE #1:c=0,e=23,dep=0,type=1,tim=482257697823
    =====================
    PARSING IN CURSOR #1 len=60 dep=0 uid=85 oct=47 lid=0 tim=482257697969 hv=919644963 ad='1a45ea38' sqlid='1y16twhvd1at3'
    BEGIN dbms_monitor.session_trace_disable(NULL, NULL); END;
    END OF STMT
    PARSE #1:c=0,e=76,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=482257697965
    EXEC #1:c=0,e=1866,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=482257699946
    

    In general, an Oracle query plan has a tree-structure and the EXPLAIN PLAN lists the nodes in “preorder” sequence; that is, the parent node is listed before its children (http://www.brpreiss.com/books/opus4/html/page258.html). The nodes are actually processed in “postorder” sequence; that is child nodes are processed in order before their parent (http://www.brpreiss.com/books/opus4/html/page259.html). The above example is an exception to the general rule though. Here are a couple of my posts on query plans.

    Explaining The EXPLAIN PLAN Using Pictures
    https://iggyfernandez.wordpress.com/2010/11/26/explaining-the-explain-plan-using-pictures/

    SQL 101: Deep Left Trees, Deep Right Trees, and Bushy Trees! Oh, My!
    https://iggyfernandez.wordpress.com/2010/11/27/sql-101-deep-left-trees-deep-right-trees-and-bushy-trees-oh-my/

    Kind regards,

    Iggy

  9. Iggy Fernandez
    December 29, 2010 at 10:21 am

    DomBrooks :

    Scalar Subqueries are one of the exceptions to the general rules.

    And the documentation certainly isn’t comprehensive in that regard.

    I agree!

  1. No trackbacks yet.

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

%d bloggers like this: