【读书笔记】【收获，不止Oracle】嵌套循环表(Nested Loops Join)

来源：互联网发布：路由器限制视频端口编辑：程序博客网时间：2024/04/29 04:08

本次试验用于探究嵌套循环表的访问次数。

1.构造t1和t2表，用于嵌套查询。

SYS@ orcl>DROP TABLE t1 CASCADE CONSTRAINTS PURGE;Table dropped.SYS@ orcl>DROP TABLE t2 CASCADE CONSTRAINTS PURGE;Table dropped.SYS@ orcl>CREATE TABLE t1(  2  id number not null,  3  n number,  4  contents varchar2(4000)  5  )  6  ;Table created.SYS@ orcl>CREATE TABLE t2(  2  id number not null,  3  t1_id number not null,  4  n number,  5  contents varchar2(4000)  6  )  7  ;Table created.

2.t1表投入100条数据，t2表投入100000条数据。、

SYS@ orcl>execute dbms_random.seed(0);PL/SQL procedure successfully completed.SYS@ orcl>INSERT INTO t1  2  SELECT   3  rownum,rownum,dbms_random.string('a',50)  4  FROM dual   5  CONNECT BY level<=100  6  ORDER BY dbms_random.random;100 rows created.SYS@ orcl>INSERT INTO t2  2  SELECT rownum,rownum,rownum,dbms_random.string('b',50)  3  FROM dual  4  CONNECT BY level<=100000  5  ORDER BY dbms_random.random; 100000 rows created.SYS@ orcl>commit;Commit complete.SYS@ orcl>select count(*) from t1;  COUNT(*)----------       100SYS@ orcl>select count(*) from t2;  COUNT(*)----------    100000

3.只是【t1.id=t2.t1_id】的情况下，执行如下语句。

SELECT /*+leading(t1) use_nl(t2)*/*FROM t1,t2WHERE t1.id=t2.t1_id;

观察执行计划

SYS@ orcl>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  6cy5taz2tutgz, child number 0-------------------------------------SELECT /*+leading(t1) use_nl(t2)*/* FROM t1,t2 WHERE t1.id=t2.t1_idPlan hash value: 1967407726-------------------------------------------------------------------------------------| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |-------------------------------------------------------------------------------------|   1 |  NESTED LOOPS      |      |      1 |    100 |    100 |00:00:00.83 |   98517 ||   2 |   TABLE ACCESS FULL| T1   |      1 |    100 |    100 |00:00:00.01 |      10 |PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|*  3 |   TABLE ACCESS FULL| T2   |    100 |      1 |    100 |00:00:00.83 |   98507 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - filter("T1"."ID"="T2"."T1_ID")Note-----   - dynamic sampling used for this statementPLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------23 rows selected.

4.多了【t1.n in(17,19)】条件

SELECT /*+leading(t1) use_nl(t2)*/*FROM t1,t2WHERE t1.id=t2.t1_idAND t1.n in(17,19);

观察执行计划

SYS@ orcl>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  bh8yn920472bt, child number 0-------------------------------------SELECT /*+leading(t1) use_nl(t2)*/* FROM t1,t2 WHERE t1.id=t2.t1_id AND t1.nin(17,19)Plan hash value: 1967407726-------------------------------------------------------------------------------------| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |-------------------------------------------------------------------------------------|   1 |  NESTED LOOPS      |      |      1 |      2 |      2 |00:00:00.01 |    1975 |PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|*  2 |   TABLE ACCESS FULL| T1   |      1 |      2 |      2 |00:00:00.01 |       4 ||*  3 |   TABLE ACCESS FULL| T2   |      2 |      1 |      2 |00:00:00.01 |    1971 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter(("T1"."N"=17 OR "T1"."N"=19))   3 - filter("T1"."ID"="T2"."T1_ID")NotePLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   - dynamic sampling used for this statement25 rows selected.

5.改变检索条件【t1.n = 19】

SELECT /*+leading(t1) use_nl(t2)*/*FROM t1,t2WHERE t1.id=t2.t1_idAND t1.n = 19;

观察执行计划

SYS@ orcl>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  7pzkjc7sd65a6, child number 0-------------------------------------SELECT /*+leading(t1) use_nl(t2)*/* FROM t1,t2 WHERE t1.id=t2.t1_id AND t1.n= 19Plan hash value: 1967407726-------------------------------------------------------------------------------------| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |-------------------------------------------------------------------------------------|   1 |  NESTED LOOPS      |      |      1 |      1 |      1 |00:00:00.01 |     990 |PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       4 ||*  3 |   TABLE ACCESS FULL| T2   |      1 |      1 |      1 |00:00:00.01 |     986 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("T1"."N"=19)   3 - filter("T1"."ID"="T2"."T1_ID")NotePLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   - dynamic sampling used for this statement25 rows selected.

6.改变检索条件【AND t1.n = 9999999999】

SYS@ orcl>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  5kpy1wbydnghk, child number 0-------------------------------------SELECT /*+leading(t1) use_nl(t2)*/* FROM t1,t2 WHERE t1.id=t2.t1_id AND t1.n= 9999999999Plan hash value: 1967407726-------------------------------------------------------------------------------------| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |-------------------------------------------------------------------------------------|   1 |  NESTED LOOPS      |      |      1 |      1 |      0 |00:00:00.01 |       3 |PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      0 |00:00:00.01 |       3 ||*  3 |   TABLE ACCESS FULL| T2   |      0 |      1 |      0 |00:00:00.01 |       0 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("T1"."N"=9999999999)   3 - filter("T1"."ID"="T2"."T1_ID")NotePLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   - dynamic sampling used for this statement25 rows selected.

总结：

循环嵌套表中驱动表(t1)返回多少条记录，被驱动表就访问多少次。

0 0