Oracle之三大连接讲解

于 2017-10-26 10:02:08 发布
阅读量671 收藏
点赞数
分类专栏： Oracle之SQL优化章节
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_33879355/article/details/78349847
版权

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")
 

一个阳光努力的程序盐
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值