query-unesting

环境：

BANNER               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production    0
PL/SQL Release 12.2.0.1.0 - Production                                                               0
CORE 12.2.0.1.0 Production                                                                                  0
TNS for Linux: Version 12.2.0.1.0 - Production                                                      0
NLSRTL Version 12.2.0.1.0 - Production                                                               0

Red Hat Enterprise Linux Server release 7.0 (Maipo)

[oracle@localhost ~]$ uname -a
Linux localhost.localdomain 3.8.13-35.3.1.el7uek.x86_64 #2 SMP Wed Jun 25 15:27:43 PDT 2014 x86_64 x86_64 x86_64 GNU/Linux

sql：SELECT * FROM HR.EMPLOYEES WHERE DEPARTMENT_ID IN(SELECT DEPARTMENT_ID FROM HR.DEPARTMENTS);

生产10053trace

 以sys用户登录

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2035.trc
SQL> oradebug event 10053 trace name context forever ,level 1;
Statement processed.
SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> SELECT * FROM HR.EMPLOYEES WHERE DEPARTMENT_ID IN(SELECT DEPARTMENT_ID FROM HR.DEPARTMENTS);

SQL> oradebug event 10053 trace name context off;
Statement processed.

查看trace

783行

SELECT "EMPLOYEES"."EMPLOYEE_ID"    "EMPLOYEE_ID",
       "EMPLOYEES"."FIRST_NAME"     "FIRST_NAME",
       "EMPLOYEES"."LAST_NAME"      "LAST_NAME",
       "EMPLOYEES"."EMAIL"          "EMAIL",
       "EMPLOYEES"."PHONE_NUMBER"   "PHONE_NUMBER",
       "EMPLOYEES"."HIRE_DATE"      "HIRE_DATE",
       "EMPLOYEES"."JOB_ID"         "JOB_ID",
       "EMPLOYEES"."SALARY"         "SALARY",
       "EMPLOYEES"."COMMISSION_PCT" "COMMISSION_PCT",
       "EMPLOYEES"."MANAGER_ID"     "MANAGER_ID",
       "EMPLOYEES"."DEPARTMENT_ID"  "DEPARTMENT_ID"
  FROM "HR"."EMPLOYEES" "EMPLOYEES"
 WHERE "EMPLOYEES"."DEPARTMENT_ID" = ANY
 (SELECT "DEPARTMENTS"."DEPARTMENT_ID" "DEPARTMENT_ID"
          FROM "HR"."DEPARTMENTS" "DEPARTMENTS")

870行

SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.

916行

SELECT "EMPLOYEES"."EMPLOYEE_ID"    "EMPLOYEE_ID",
       "EMPLOYEES"."FIRST_NAME"     "FIRST_NAME",
       "EMPLOYEES"."LAST_NAME"      "LAST_NAME",
       "EMPLOYEES"."EMAIL"          "EMAIL",
       "EMPLOYEES"."PHONE_NUMBER"   "PHONE_NUMBER",
       "EMPLOYEES"."HIRE_DATE"      "HIRE_DATE",
       "EMPLOYEES"."JOB_ID"         "JOB_ID",
       "EMPLOYEES"."SALARY"         "SALARY",
       "EMPLOYEES"."COMMISSION_PCT" "COMMISSION_PCT",
       "EMPLOYEES"."MANAGER_ID"     "MANAGER_ID",
       "EMPLOYEES"."DEPARTMENT_ID"  "DEPARTMENT_ID"
  FROM "HR"."DEPARTMENTS" "DEPARTMENTS", "HR"."EMPLOYEES" "EMPLOYEES"
 WHERE "EMPLOYEES"."DEPARTMENT_ID" = "DEPARTMENTS"."DEPARTMENT_ID"

trace文件

 http://download.csdn.net/detail/bjchangxiao/9799203