操作系统:WINDOWS 7 旗舰版 64位
硬件: 笔记本
ORACLE DATABASE: 11.2.0.4 64 位
RAC: NO
试验目的:测试WITH与临时表哪种方式可以优化嵌套表连接
试验结论:WITH无法优化嵌套表连接,而且还会加大一倍嵌套表连接的成本。使用临时表分解复杂嵌套表连接可以很好的优化性能。
SQL>alter session set STATISTICS_LEVEL = ALL; --不设置无法获得A-ROWS等信息
1、驱动表和被驱动表都使用with,表连接成本是非with的两倍(执行了驱动表扫描两次,对被驱动表访问了20次(20次的扫描,产生了20万条中间结果集))
with
t1 as (select object_id from lixia.objects t10 where rownum<11),
t2 as (select * from lixia.objects t20 where rownum<10001 )
select /*+ use_nl(t1,t2)*/ t2.object_id
from t1,t2 where t1.object_id=t2.object_id;
OBJECT_ID
----------
20
46
28
15
29
3
25
41
54
40
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
SQL_ID 6z21871u64pa4, child number 0
-------------------------------------
with t1 as (select object_id from lixia.objects t10 where rownum<11),
t2 as (select * from lixia.objects t20 where rownum<10001 ) select
/*+ use_nl(t1,t2)*/ t2.object_id from t1,t2 where
t1.object_id=t2.object_id
Plan hash value: 2858959367
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | | 20 |00:00:00.31 | 2650 | 2826 |
| 1 | NESTED LOOPS | | 2 | 1 | 20 |00:00:00.31 | 2650 | 2826 |
| 2 | VIEW | | 2 | 10 | 20 |00:00:00.07 | 8 | 26 |
|* 3 | COUNT STOPKEY | | 2 | | 20 |00:00:00.07 | 8 | 26 |
| 4 | TABLE ACCESS FULL| OBJECTS | 2 | 10 | 20 |00:00:00.07 | 8 | 26 |
|* 5 | VIEW | | 20 | 1 | 20 |00:00:00.24 | 2642 | 2800 |
|* 6 | COUNT STOPKEY | | 20 | | 200K|00:00:00.21 | 2642 | 2800 |
| 7 | TABLE ACCESS FULL| OBJECTS | 20 | 10000 | 200K|00:00:00.14 | 2642 | 2800 |
---------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM<11)
5 - filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
6 - filter(ROWNUM<10001)
2、驱动表使用WITH 被驱动表使用堆表,驱动表只扫描一次,被驱动表只扫描10次,而且扫描被驱动表的过程中产生的中间结果集只有10条。
SQL> with
2 t1 as (select object_id from lixia.objects t10 where rownum<11)
3 select /*+ use_nl(t1,t2)*/ t2.object_id
4 from t1,lixia.t20 t2 where t1.object_id=t2.object_id;
OBJECT_ID
----------
20
46
28
15
29
3
25
41
54
40
已选择10行。
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
SQL_ID 8k4jyrmx3b420, child number 0
-------------------------------------
with t1 as (select object_id from lixia.objects t10 where rownum<11)
select /*+ use_nl(t1,t2)*/ t2.object_id from t1,lixia.t20 t2 where
t1.object_id=t2.object_id
Plan hash value: 127688620
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.06 | 1345 | 39 |
| 1 | NESTED LOOPS | | 1 | 1 | 10 |00:00:00.06 | 1345 | 39 |
| 2 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 4 | 13 |
|* 3 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 4 | 13 |
| 4 | TABLE ACCESS FULL| OBJECTS | 1 | 10 | 10 |00:00:00.01 | 4 | 13 |
|* 5 | TABLE ACCESS FULL | T20 | 10 | 1 | 10 |00:00:00.04 | 1341 | 26 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
3 - filter(ROWNUM<11)
5 - filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
已选择29行。
3、驱动表使用WITH 被驱动表使用临时表,驱动表只扫描一次,被驱动表只扫描10次,而且扫描被驱动表的过程中产生的中间结果集只有10条。
SQL> create global temporary table t21 as
2 select * from lixia.objects t20 where rownum<10001 ;
表已创建。
SQL> select count(1) from t21;
COUNT(1)
----------
0
SQL> insert into lixia.t21
2 select * from lixia.objects t20 where rownum<10001 ;
已创建 10000 行。
SQL> select count(*) from lixia.t21;
COUNT(*)
----------
10000
SQL> with
2 t1 as (select object_id from lixia.objects t10 where rownum<11)
3 select /*+ use_nl(t1,t2)*/ t2.object_id
4 from t1,lixia.t21 t2 where t1.object_id=t2.object_id;
OBJECT_ID
----------
20
46
28
15
29
3
25
41
54
40
已选择10行。
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
SQL_ID 52ytbv6cvh158, child number 0
-------------------------------------
with t1 as (select object_id from lixia.objects t10 where rownum<11)
select /*+ use_nl(t1,t2)*/ t2.object_id from t1,lixia.t21 t2 where
t1.object_id=t2.object_id
Plan hash value: 930774503
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.05 | 1355 | 13 |
| 1 | NESTED LOOPS | | 1 | 1 | 10 |00:00:00.05 | 1355 | 13 |
| 2 | VIEW | | 1 | 10 | 10 |00:00:00.04 | 4 | 13 |
|* 3 | COUNT STOPKEY | | 1 | | 10 |00:00:00.04 | 4 | 13 |
| 4 | TABLE ACCESS FULL| OBJECTS | 1 | 10 | 10 |00:00:00.04 | 4 | 13 |
|* 5 | TABLE ACCESS FULL | T21 | 10 | 1 | 10 |00:00:00.01 | 1351 | 0 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
3 - filter(ROWNUM<11)
5 - filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
已选择29行。
4、驱动表和被驱动表使用临时表,驱动表只扫描一次,被驱动表只扫描10次,而且扫描被驱动表的过程中产生的中间结果集只有10条。
create global temporary table lixia.t22 as
select object_id from lixia.objects where 1=0;
insert into lixia.t22
select object_id from lixia.objects where rownum<11;
insert into lixia.t21
select * from lixia.objects t20 where rownum<10001 ;
select /*+ use_nl(t1,t2)*/ t2.object_id
from lixia.t22 t1,lixia.t21 t2 where t1.object_id=t2.object_id;
SQL> select /*+ use_nl(t1,t2)*/ t2.object_id
2 from lixia.t22 t1,lixia.t21 t2 where t1.object_id=t2.object_id;
OBJECT_ID
----------
20
46
28
15
29
3
25
41
54
40
已选择10行。
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
SQL_ID 60hapd1wvxn51, child number 0
-------------------------------------
select /*+ use_nl(t1,t2)*/ t2.object_id from lixia.t22 t1,lixia.t21 t2
where t1.object_id=t2.object_id
Plan hash value: 3737195244
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | | 10 |00:00:00.09 | 30137 |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
| 1 | NESTED LOOPS | | 2 | 1 | 10 |00:00:00.09 | 30137 |
| 2 | TABLE ACCESS FULL| T21 | 2 | 1 | 10000 |00:00:00.01 | 136 |
|* 3 | TABLE ACCESS FULL| T22 | 10000 | 1 | 10 |00:00:00.08 | 30001 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
-----
- dynamic sampling used for this statement (level=2)
已选择25行。
SQL> select /*+ use_nl(t1,t2) order */ t2.object_id
2 from lixia.t22 t1,lixia.t21 t2 where t1.object_id=t2.object_id;
OBJECT_ID
----------
20
46
28
15
29
3
25
41
54
40
已选择10行。
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
SQL_ID g52cgs2k4bfnc, child number 0
-------------------------------------
select /*+ use_nl(t1,t2) order */ t2.object_id from lixia.t22
t1,lixia.t21 t2 where t1.object_id=t2.object_id
Plan hash value: 1229608388
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 1355 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 10 | 10 |00:00:00.01 | 1355 |
| 2 | TABLE ACCESS FULL| T22 | 1 | 10 | 10 |00:00:00.01 | 4 |
|* 3 | TABLE ACCESS FULL| T21 | 10 | 1 | 10 |00:00:00.01 | 1351 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
-----
- dynamic sampling used for this statement (level=2)
已选择25行。
5、直接使用子查询,成本只有驱动表和被驱动表都使用WITH的一半(通过SQL时间执行时间判断)
SQL> select /*+ use_nl(t1,t2)*/
2 t1.OBJECT_ID from (select object_id from lixia.objects where rownum<11)
3 t1,(select * from lixia.objects where rownum<10001 )t2 where t1.object_id=t2.object_id;
OBJECT_ID
----------
20
46
28
15
29
3
25
41
54
40
已选择10行。
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
SQL_ID 6h8ur411uw5xa, child number 0
-------------------------------------
select /*+ use_nl(t1,t2)*/ t1.OBJECT_ID from (select object_id from
lixia.objects where rownum<11) t1,(select * from lixia.objects where
rownum<10001 )t2 where t1.object_id=t2.object_id
Plan hash value: 2858959367
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.16 | 1325 | 1413 |
| 1 | NESTED LOOPS | | 1 | 1 | 10 |00:00:00.16 | 1325 | 1413 |
| 2 | VIEW | | 1 | 10 | 10 |00:00:00.03 | 4 | 13 |
|* 3 | COUNT STOPKEY | | 1 | | 10 |00:00:00.03 | 4 | 13 |
| 4 | TABLE ACCESS FULL| OBJECTS | 1 | 10 | 10 |00:00:00.03 | 4 | 13 |
|* 5 | VIEW | | 10 | 1 | 10 |00:00:00.13 | 1321 | 1400 |
|* 6 | COUNT STOPKEY | | 10 | | 100K|00:00:00.11 | 1321 | 1400 |
| 7 | TABLE ACCESS FULL| OBJECTS | 10 | 10000 | 100K|00:00:00.07 | 1321 | 1400 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
---------------------------------------------------
3 - filter(ROWNUM<11)
5 - filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
6 - filter(ROWNUM<10001)
已选择28行。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21582653/viewspace-1366727/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21582653/viewspace-1366727/