with clause excution plan

Birmingham写过一篇关于with语句的文章。with语句在oracle中会被转换成inline view或者gobal temporary table。

本文主要测试了一下执行计划的输出和10046 trace。

x.sql显示执行计划出自tanel poder

rem
rem with格式
rem
SQL> with d as
  2  (select 
  3     department_id,count(*) as count
  4     from departments 
  5     group by department_id)
  6  select e.last_name as employee_name,
  7     d.count as employee_count
  8  from employees e,d
  9  where e.department_id = d.department_id;


SQL> @x
Display execution plan for last statement for this session from library cache...

PLAN_TABLE_OUTPUT
SQL_ID  9s04kdwwd6g4s, child number 0
-------------------------------------
with d as (select  department_id,count(*) as count  from departments
group by department_id) select e.last_name as employee_name,  d.count
as employee_count from employees e,d where e.department_id =
d.department_id

Plan hash value: 3632372596

----------------------------------------------------------------------------
| Id  | Operation               | Name       | E-Rows |E-Bytes| Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |        |       |     2 (100)|
|   1 |  NESTED LOOPS           |            |    106 |  2544 |     2   (0)|
|   2 |   TABLE ACCESS FULL     | EMPLOYEES  |    107 |  1177 |     2   (0)|
|   3 |   VIEW PUSHED PREDICATE |            |      1 |    13 |     0   (0)|
|*  4 |    FILTER               |            |        |       |            |
|   5 |     SORT AGGREGATE      |            |      1 |     4 |            |
|*  6 |      INDEX UNIQUE SCAN  | DEPT_ID_PK |      1 |     4 |     0   (0)|
----------------------------------------------------------------------------

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

   4 - filter(COUNT(*)>0)
   6 - access("DEPARTMENT_ID"="E"."DEPARTMENT_ID")

rem
rem subquery格式
rem
SQL> alter system flush shared_pool;

System altered.

SQL> select e.last_name as employee_name,
  2     d.count as employee_count
  3  from employees e,
  4     (select department_id,count(*) as count
  5     from departments 
  6     group by department_id) d
  7  where e.department_id = d.department_id;



SQL> @x
Display execution plan for last statement for this session from library cache...

PLAN_TABLE_OUTPUT
SQL_ID  auysq69synk7x, child number 0
-------------------------------------
select e.last_name as employee_name,  d.count as employee_count from
employees e,  (select department_id,count(*) as count  from departments
 group by department_id) d where e.department_id = d.department_id

Plan hash value: 3632372596

----------------------------------------------------------------------------
| Id  | Operation               | Name       | E-Rows |E-Bytes| Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |        |       |     2 (100)|
|   1 |  NESTED LOOPS           |            |    106 |  2544 |     2   (0)|
|   2 |   TABLE ACCESS FULL     | EMPLOYEES  |    107 |  1177 |     2   (0)|
|   3 |   VIEW PUSHED PREDICATE |            |      1 |    13 |     0   (0)|
|*  4 |    FILTER               |            |        |       |            |
|   5 |     SORT AGGREGATE      |            |      1 |     4 |            |
|*  6 |      INDEX UNIQUE SCAN  | DEPT_ID_PK |      1 |     4 |     0   (0)|
----------------------------------------------------------------------------

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

   4 - filter(COUNT(*)>0)
   6 - access("DEPARTMENT_ID"="E"."DEPARTMENT_ID")

可以看到2种语句格式下 oracle的执行计划是相同的。

在使用with语句的时候,会经常看到使用/*+ MATERIALIZE */的情况,这个hint是一个未公开的hint。

SQL> with d as
  2  (select /*+ MATERIALIZE */
  3     department_id,count(*) as count
  4     from departments 
  5     group by department_id)
  6  select e.last_name as employee_name,
  7     d.count as employee_count
  8  from employees e,d
  9  where e.department_id = d.department_id;
SQL> @x
Display execution plan for last statement for this session from library cache...

PLAN_TABLE_OUTPUT
SQL_ID  0cjfwgbmmda54, child number 1
-------------------------------------
with d as (select /*+ MATERIALIZE */  department_id,count(*) as count
from departments  group by department_id) select e.last_name as
employee_name,  d.count as employee_count from employees e,d where
e.department_id = d.department_id
Plan hash value: 3417278743

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | E-Rows |E-Bytes| Cost (%CPU)|  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |        |       |     5 (100)|       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION |                             |        |       |            |       |       |          |
|   2 |   LOAD AS SELECT           |                             |        |       |            |   270K|   270K|  270K (0)|
|   3 |    SORT GROUP BY NOSORT    |                             |     27 |   108 |     1   (0)|       |       |          |
|   4 |     INDEX FULL SCAN        | DEPT_ID_PK                  |     27 |   108 |     1   (0)|       |       |          |
|*  5 |   HASH JOIN                |                             |    106 |  3922 |     4   (0)|  1969K|  1969K| 1294K (0)|
|   6 |    VIEW                    |                             |     27 |   702 |     2   (0)|       |       |          |
|   7 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D666B_481184D6 |     27 |   108 |     2   (0)|       |       |          |
|   8 |    TABLE ACCESS FULL       | EMPLOYEES                   |    107 |  1177 |     2   (0)|       |       |          |
---------------------------------------------------------------------------------------------------------------------------

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

   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

可以看到加了hint的执行计划与上面不加任何hint的语句的执行计划有很大的不同。

此条目发表在CBO分类目录。将固定链接加入收藏夹。

发表评论

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 / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s