1.nl连接
/*
结论: NL连接中,驱动表放回的数据条数,决定着被驱动表的访问次数
*/
/*
结论: NL连接中,驱动表被访问0或者1次,被驱动表被访问0次或者N次,N由驱动表返回的结果集的条数来定)
*/
名字解释:E-Rows:预期返回数据数量,A-Rows:实际返回数据数量
--环境构造
--研究Nested Loops Join的表访问次数前准备工作
DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS PURGE;
CREATE TABLE t1 (
id NUMBER NOT NULL,
n NUMBER,
contents VARCHAR2(4000)
)
;
CREATE TABLE t2 (
id NUMBER NOT NULL,
t1_id NUMBER NOT NULL,
n NUMBER,
contents VARCHAR2(4000)
)
;
execute dbms_random.seed(0);
INSERT INTO t1
SELECT rownum, rownum, dbms_random.string('a', 50)
FROM dual
CONNECT BY level <= 100
ORDER BY dbms_random.random;
INSERT INTO t2 SELECT rownum, rownum, rownum, dbms_random.string('b', 50) FROM dual CONNECT BY level <= 100000
ORDER BY dbms_random.random;
COMMIT;
select count(*) from t1;
select count(*) from t2;
--我们用设置statistics_level=all的方式来观察如下表连接语句的执行计划:
--T2表被访问100次(驱动表访问1次,被驱动表访问100次)
--这个set linesize 1000对dbms_xplan.display_cursor还是有影响的,如果没有设置,默认情况下的输出,将会少了很多列,如BUFFERS等
Set linesize 1000
alter session set statistics_level=all ;
SELECT /*+ leading(t1) use_nl(t2)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id;
--略去记录结果
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.94 | 100K|
| 1 | NESTED LOOPS | | 1 | 100 | 100 |00:00:00.94 | 100K|
| 2 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 14 |
|* 3 | TABLE ACCESS FULL| T2 | 100 | 1 | 100 |00:00:00.94 | 100K|
-------------------------------------------------------------------------------------
3 - filter("T1"."ID"="T2"."T1_ID")
---换个语句,这次T2表被访问2次(驱动表访问1次,被驱动表访问2次)
Set linesize 1000
alter session set statistics_level=all ;
SELECT /*+ leading(t1) use_nl(t2) */ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(17, 19);
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.02 | 2019 |
| 1 | NESTED LOOPS | | 1 | 2 | 2 |00:00:00.02 | 2019 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 2 | 2 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| T2 | 2 | 1 | 2 |00:00:00.02 | 2011 |
-------------------------------------------------------------------------------------
2 - filter(("T1"."N"=17 OR "T1"."N"=19))
3 - filter("T1"."ID"="T2"."T1_ID")
--继续换个语句,这次T2表被访问1次(驱动表访问1次,被驱动表访问1次)
Set linesize 1000
alter session set statistics_level=all ;
SELECT /*+ leading(t1) use_nl(t2) */ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n = 19;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1014 |
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 1014 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 1 | 1 |00:00:00.01 | 1006 |
-------------------------------------------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID"="T2"."T1_ID")
---接下来,T2表居然被访问0次(驱动表访问1次,被驱动表访问0次)
SELECT /*+ leading(t1) use_nl(t2) */ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n = 999999999;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 7 |
| 1 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 7 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 0 |00:00:00.01 | 7 |
|* 3 | TABLE ACCESS FULL| T2 | 0 | 1 | 0 |00:00:00.01 | 0 |
-------------------------------------------------------------------------------------
2 - filter("T1"."N"=999999999)
3 - filter("T1"."ID"="T2"."T1_ID")
---到最后,不只是T2表被访问0次,连T1表也访问0次
SELECT /*+ leading(t1) use_nl(t2) */ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND 1=2;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 |
|* 1 | FILTER | | 1 | | 0 |00:00:00.01 |
| 2 | NESTED LOOPS | | 0 | 100 | 0 |00:00:00.01 |
| 3 | TABLE ACCESS FULL| T1 | 0 | 100 | 0 |00:00:00.01 |
|* 4 | TABLE ACCESS FULL| T2 | 0 | 1 | 0 |00:00:00.01 |
----------------------------------------------------------------------------
1 - filter(NULL IS NOT NULL)
4 - filter("T1"."ID"="T2"."T1_ID")
--分析T2表被访问次数不同的原因
---解释T2表为啥被访问100次
select count(*) from t1;
COUNT(*)
----------
100
---解释T2表为啥被访问2次
select count(*) from t1 where t1.n in (17,19);
COUNT(*)
----------
2
---解释T2表为啥被访问1次
select count(*) from t1 where t1.n = 19;
COUNT(*)
----------
1
---解释T2表为啥被访问0次
select count(*) from t1 where t1.n = 999999999;
COUNT(*)
----------
0
2.merge连接排序
/*
结论: 排序合并连接中,两表都是只被访问0次或者1次,和HASH 连接一样)
*/
--环境构造
--研究Nested Loops Join的表访问次数前准备工作
DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS PURGE;
CREATE TABLE t1 (
id NUMBER NOT NULL,
n NUMBER,
contents VARCHAR2(4000)
)
;
CREATE TABLE t2 (
id NUMBER NOT NULL,
t1_id NUMBER NOT NULL,
n NUMBER,
contents VARCHAR2(4000)
)
;
execute dbms_random.seed(0);
INSERT INTO t1
SELECT rownum, rownum, dbms_random.string('a', 50)
FROM dual
CONNECT BY level <= 100
ORDER BY dbms_random.random;
INSERT INTO t2 SELECT rownum, rownum, rownum, dbms_random.string('b', 50) FROM dual CONNECT BY level <= 100000
ORDER BY dbms_random.random;
COMMIT;
select count(*) from t1;
select count(*) from t2;
--Merge Sort Join中 T2表只会被访问1次或0次(驱动表访问1次,被驱动表访问1次)
set linesize 1000
SELECT /*+ leading(t1) use_merge(t2)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.09 | 1012 | | | |
| 1 | MERGE JOIN | | 1 | 100 | 100 |00:00:00.09 | 1012 | | | |
| 2 | SORT JOIN | | 1 | 100 | 100 |00:00:00.01 | 7 | 11264 | 11264 |10240 (0)|
| 3 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 7 | | | |
|* 4 | SORT JOIN | | 100 | 100K| 100 |00:00:00.09 | 1005 | 9266K| 1184K| 8236K (0)|
| 5 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.02 | 1005 | | | |
-----------------------------------------------------------------------------------------------------------------
4 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")
--Merge Sort Join中T2表被访问0次的情况
set linesize 1000
SELECT /*+ leading(t1) use_merge(t2)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id
and t1.n=999999999;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 7 | | | |
| 1 | MERGE JOIN | | 1 | 1 | 0 |00:00:00.01 | 7 | | | |
| 2 | SORT JOIN | | 1 | 1 | 0 |00:00:00.01 | 7 | 1024 | 1024 | |
|* 3 | TABLE ACCESS FULL| T1 | 1 | 1 | 0 |00:00:00.01 | 7 | | | |
|* 4 | SORT JOIN | | 0 | 100K| 0 |00:00:00.01 | 0 | 7250K| 1073K| |
| 5 | TABLE ACCESS FULL| T2 | 0 | 100K| 0 |00:00:00.01 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------
3 - filter("T1"."N"=999999999)
4 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")
--Merge Sort Join中T1和T2表都访问0次的情况
set linesize 1000
SELECT /*+ leading(t1) use_merge(t2)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id
and 1=2;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | | | |
|* 1 | FILTER | | 1 | | 0 |00:00:00.01 | | | |
| 2 | MERGE JOIN | | 0 | 100 | 0 |00:00:00.01 | | | |
| 3 | SORT JOIN | | 0 | 100 | 0 |00:00:00.01 | 73728 | 73728 | |
| 4 | TABLE ACCESS FULL| T1 | 0 | 100 | 0 |00:00:00.01 | | | |
|* 5 | SORT JOIN | | 0 | 100K| 0 |00:00:00.01 | 7250K| 1073K| |
| 6 | TABLE ACCESS FULL| T2 | 0 | 100K| 0 |00:00:00.01 | | | |
--------------------------------------------------------------------------------------------------------
1 - filter(NULL IS NOT NULL)
5 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")
3.hash连接
/*
结论: NL连接中,驱动表被访问0或者1次,被驱动表也是被访问0次或者1次,绝大部分场景是驱动表和被驱动表被各访问1次)
*/
--环境构造
--研究Nested Loops Join的表访问次数前准备工作
DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS PURGE;
CREATE TABLE t1 (
id NUMBER NOT NULL,
n NUMBER,
contents VARCHAR2(4000)
)
;
CREATE TABLE t2 (
id NUMBER NOT NULL,
t1_id NUMBER NOT NULL,
n NUMBER,
contents VARCHAR2(4000)
)
;
execute dbms_random.seed(0);
INSERT INTO t1
SELECT rownum, rownum, dbms_random.string('a', 50)
FROM dual
CONNECT BY level <= 100
ORDER BY dbms_random.random;
INSERT INTO t2 SELECT rownum, rownum, rownum, dbms_random.string('b', 50) FROM dual CONNECT BY level <= 100000
ORDER BY dbms_random.random;
COMMIT;
select count(*) from t1;
select count(*) from t2;
--Hash Join中 T2表只会被访问1次或0次(驱动表访问1次,被驱动表访问1次)
set linesize 1000
SELECT /*+ leading(t1) use_hash(t2) */ *
FROM t1, t2
WHERE t1.id = t2.t1_id;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.07 | 1019 | | | |
|* 1 | HASH JOIN | | 1 | 100 | 100 |00:00:00.07 | 1019 | 742K| 742K| 1178K (0)|
| 2 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS FULL| T2 | 1 | 111K| 100K|00:00:00.02 | 1012 | | | |
----------------------------------------------------------------------------------------------------------------
1 - access("T1"."ID"="T2"."T1_ID")
--Hash Join中T2表被访问0次的情况
SELECT /*+ leading(t1) use_hash(t2)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id
and t1.n=999999999;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 7 | | | |
|* 1 | HASH JOIN | | 1 | 1 | 0 |00:00:00.01 | 7 | 676K| 676K| 205K (0)|
|* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 0 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS FULL| T2 | 0 | 111K| 0 |00:00:00.01 | 0 | | | |
----------------------------------------------------------------------------------------------------------------
1 - access("T1"."ID"="T2"."T1_ID")
2 - filter("T1"."N"=999999999)
--Hash Join中T1和T2表都访问0次的情况
SELECT /*+ leading(t1) use_hash(t2)*/ *
FROM t1, t2
WHERE t1.id = t2.t1_id
and 1=2;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | | | |
|* 1 | FILTER | | 1 | | 0 |00:00:00.01 | | | |
|* 2 | HASH JOIN | | 0 | 100 | 0 |00:00:00.01 | 732K| 732K| |
| 3 | TABLE ACCESS FULL| T1 | 0 | 100 | 0 |00:00:00.01 | | | |
| 4 | TABLE ACCESS FULL| T2 | 0 | 111K| 0 |00:00:00.01 | | | |
-------------------------------------------------------------------------------------------------------
1 - filter(NULL IS NOT NULL)
2 - access("T1"."ID"="T2"."T1_ID")