Oracle表联结之嵌套循环

34tg

1.单表访问： 分表，分区，建索引，全表扫描---开并行， 永远把它 放内存，压缩


2.多表关联，任何时刻只能是2个表关联，得到的结果集再和其他表关联。
3.嵌套循环：Oracle从较小结果集(驱动表/外部表)中读取一行，然后和较大结果集(被探查表/内部表)中的所有数据逐条进行比较(嵌套循环可以用于非等值连接)，如果符合规则，就放入结果集中，然后取较小结果集的下一条数据继续进行循环，直到结束。嵌套循环只适合输出少量结果集或者是用于快速输出结果集。其实相当于双层FOR循环。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID bv300dy9b7gyn, child number 0 ------------------------------------- select /*+ first_rows */ e.ename,e.job,d.dname from emp e,dept d  where e.deptno=d.deptno and e.sal<2000 Plan hash value: 3625962092 ----------------------------------------------------------------------------------------------------------- | Id  | Operation       | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | ----------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT      |        |      1 |  | 7 |00:00:00.14 |      18 | 8 | |   1 |  NESTED LOOPS       |        |      1 |      4 | 7 |00:00:00.14 |      18 | 8 | |   2 |   NESTED LOOPS       |        |      1 |      4 | 7 |00:00:00.14 |      11 | 7 | |*  3 |    TABLE ACCESS FULL      | EMP     |      1 |      4 | 7 |00:00:00.12 |       7 | 6 | |*  4 |    INDEX UNIQUE SCAN      | PK_DEPT |      7 |      1 | 7 |00:00:00.01 |       4 | 1 | |   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      7 |      1 | 7 |00:00:00.01 |       7 | 1 | ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    3 - filter("E"."SAL"<2000)    4 - access("E"."DEPTNO"="D"."DEPTNO") 24 rows selected.

离关键字近的是驱动表，嵌套循环的rows是错误的，嵌套循环的算法，比如a NL b，如a表有1000条，从a表中取1000条数据，扫描一次a，把这1000条数据传给b，然后b表被扫描1000次，那么取出的a表的数据放在什么地方？匹配完一条然后立马返回，NL不需要PGA，因为不用缓存数据，如果多层NL，仍然是存PGA，多层NL容易引起CBC，
嵌套循环中，过滤后返回结果集的小的当驱动表，在外连接中，嵌套循环不能修改驱动表，在嵌套循环中，被驱动表的连接列一定要有索引，从上面执行计划可以看到E的deptno传值给D表的deptno，驱动表的连接列不用建索引，
nl 必须是驱动表返回数据量很少的时候才走，在sql语句中有count，group by，distinct，sum等关键字，不能走NL，如果OLTP系统，有大量的distinct，只能说明表设计有问题，用中间表把所有的关联去重解决distinct，
如果在执行计划里面有很多NL，从最里面开始搞，如果最里面错误了，那么外面的NL全部错误，由里向外不断看NL.
怎样判断NL是否是对的？1，看驱动表返回的数据量，2，看被驱动表是否走索引，3.看最终返回多少结果集。那么第3条最重要。最终返回多少结果集决定是否走NL还是HASH.

如果A NL B，返回10w条数据，如果a:b=1:1,那么a至少返回10w条数据，然后b表被扫描10w次，如果a：b=1:10，那么a至少返回1w数据，那么b表被扫描1w次，b表走索引，b表走一次索引，回表10条数据，那么b表总回表次数是10w次，所以在NL中，被驱动表不管被扫描多少次，那么回表次数是最终返回数据条数，所以嵌套循环不适合大量数据，根本原因在于回表或者回表再过滤，如果不用回表或回表再过滤，那么NL非常有效。


被驱动表的连接列要基数很高，如果基数很低，不能走NL，如1：1w，1：N，然后N太大

NL只需要SGA，不需要PGA，NL支持非等值jion，而HASH join只支持等值关联。

判断是否走NL和HASH，根据最终返回的结果集来判断，其次驱动表返回的行数，再是被驱动表的jion列的基数。

错误NL，1.单次返回大量数据，如100w


附：查询访问表所有字段的sql信息：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

WITH t AS (SELECT a.object_name "表名",          a.sql_id,          c.sql_text,          c.executions,          (regexp_count(prjection, ',', 1) + 1) / 2 "访问列数",          COUNT(b.column_name) "总列数",          d.bytes / 1024 / 1024 "表体积_MB"     FROM v$sql_plan a, dba_tab_columns b, v$sql c, dba_segments d    WHERE a.object_owner = b.owner      AND a.object_name = b.table_name      AND a.object_owner = 'EOL'      AND a.sql_id = c.sql_id      AND a.object_name = d.segment_name      AND a.object_owner = d.owner    GROUP BY (regexp_count(prjection, ',', 1) + 1) / 2，a.object_name,             a.sql_id,             c.sql_text,             c.executions,             d.bytes / 1024 / 1024) SELECT * FROM t WHERE t.访问列数 = t.总列数 ORDER BY 表体积_MB,executions DESC;