【读书笔记】【收获，不止Oracle】嵌套循环与索引

来源：互联网发布：电火花快走丝的编程编辑：程序博客网时间：2024/04/29 12:29

1.1 使用HINT强制嵌套循环方式连接两个表

select /*+leading(t1) use_nl(t2) */*from t1,t2where t1.id = t2.t1_idand t1.n = 19;

1.2 执行结果

SCOTT@ orcl>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  fuqy42vhj5n6k, child number 0-------------------------------------select /*+leading(t1) use_nl(t2) */* from t1,t2 where t1.id = t2.t1_id andt1.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 |    1014 |PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|*  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 |-------------------------------------------------------------------------------------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.

结果可知：无索引的情况下 Buffers产生了1014。

2.1 不使用HINT时，oracle本身会选择哈希连接

select *from t1,t2where t1.id = t2.t1_idand t1.n = 19;

2.2 查看执行结果

SCOTT@ orcl>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  agmm8r3nv4gcg, child number 0-------------------------------------select * from t1,t2 where t1.id = t2.t1_id and t1.n = 19Plan hash value: 1838229974----------------------------------------------------------------------------------------------------------------| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |----------------------------------------------------------------------------------------------------------------|*  1 |  HASH JOIN         |      |      1 |      1 |      1 |00:00:00.83 |    1013 |   741K|   741K|  306K (0)||*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|   3 |   TABLE ACCESS FULL| T2   |      1 |    102K|    100K|00:00:00.20 |    1006 |       |       |          |----------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("T1"."ID"="T2"."T1_ID")   2 - filter("T1"."N"=19)Note-----PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   - dynamic sampling used for this statement24 rows selected.

使用HINT时，Buffers 产生了 1014

Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers ||   1 |  NESTED LOOPS      |      |      1 |      1 |      1 |00:00:00.01 |    1014 |

不使用HINT时，Buffers 产生了 1020 。即使产生的buffers比NESTED LOOPS多，编译器还是选择了HASH JOIN。

| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |----------------------------------------------------------------------------------------------------------------|*  1 |  HASH JOIN         |      |      1 |      1 |      1 |00:00:00.83 |    1013 |   741K|   741K|  306K (0)||*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |

3.1 在t1的n列上建立索引

SCOTT@ orcl>create index t1_n on t1(n);Index createdselect /*+leading(t1) use_nl(t2) */*from t1,t2where t1.id = t2.t1_idand t1.n = 19;

3.2 查看执行结果

SCOTT@ orcl>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  fuqy42vhj5n6k, child number 0-------------------------------------select /*+leading(t1) use_nl(t2) */* from t1,t2 where t1.id = t2.t1_id and t1.n = 19Plan hash value: 76617097-----------------------------------------------------------------------------------------------| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |-----------------------------------------------------------------------------------------------|   1 |  NESTED LOOPS                |      |      1 |      1 |      1 |00:00:00.01 |    1009 ||   2 |   TABLE ACCESS BY INDEX ROWID| T1   |      1 |      1 |      1 |00:00:00.01 |       3 |PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|*  3 |    INDEX RANGE SCAN          | T1_N |      1 |      1 |      1 |00:00:00.01 |       2 ||*  4 |   TABLE ACCESS FULL          | T2   |      1 |      1 |      1 |00:00:00.01 |    1006 |-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - access("T1"."N"=19)   4 - filter("T1"."ID"="T2"."T1_ID")NotePLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   - dynamic sampling used for this statement25 rows selected.

在t1的n列上建立索引之后，产生的Buffers：1012

-----------------------------------------------------------------------------------------------| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |-----------------------------------------------------------------------------------------------|   1 |  NESTED LOOPS                |      |      1 |      1 |      1 |00:00:00.01 |    1009 ||   2 |   TABLE ACCESS BY INDEX ROWID| T1   |      1 |      1 |      1 |00:00:00.01 |       3 |

没有建索引之前产生的Buffers：1014

Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |-------------------------------------------------------------------------------------|   1 |  NESTED LOOPS      |      |      1 |      1 |      1 |00:00:00.01 |    1014 |

建立索引易后，性能方面有了少许的提升。

两者之间的差异在于t1表，

无索引：TABLE ACCESS FULL 8

有索引：INDEX RANGE SCAN 2

4.1 在t2的t1_id上建立索引，继续提升性能

SCOTT@ orcl>create index t2_t1_id on t2(t1_id);Index createdselect /*+leading(t1) use_nl(t2) */*from t1,t2where t1.id = t2.t1_idand t1.n = 19;

4.2 查看执行计划

SCOTT@ orcl>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  fuqy42vhj5n6k, child number 0-------------------------------------select /*+leading(t1) use_nl(t2) */* from t1,t2 where t1.id = t2.t1_id and t1.n = 19Plan hash value: 2669480776-------------------------------------------------------------------------------------------------------------| Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |-------------------------------------------------------------------------------------------------------------|   1 |  TABLE ACCESS BY INDEX ROWID  | T2       |      1 |      1 |      1 |00:00:00.01 |       7 |      1 ||   2 |   NESTED LOOPS                |          |      1 |      1 |      3 |00:00:00.01 |       6 |      1 |PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|   3 |    TABLE ACCESS BY INDEX ROWID| T1       |      1 |      1 |      1 |00:00:00.01 |       3 |      0 ||*  4 |     INDEX RANGE SCAN          | T1_N     |      1 |      1 |      1 |00:00:00.01 |       2 |      0 ||*  5 |    INDEX RANGE SCAN           | T2_T1_ID |      1 |      1 |      1 |00:00:00.01 |       3 |      1 |-------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   4 - access("T1"."N"=19)   5 - access("T1"."ID"="T2"."T1_ID")PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Note-----   - dynamic sampling used for this statement26 rows selected.

之前建立的索引

-----------------------------------------------------------------------------------------------| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |-----------------------------------------------------------------------------------------------|   1 |  NESTED LOOPS                |      |      1 |      1 |      1 |00:00:00.01 |    1009 ||   2 |   TABLE ACCESS BY INDEX ROWID| T1   |      1 |      1 |      1 |00:00:00.01 |       3 |

新增的索引之后，buffers从1012降到了7。性能有了巨大提升。

-------------------------------------------------------------------------------------------------------------| Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |-------------------------------------------------------------------------------------------------------------|   1 |  TABLE ACCESS BY INDEX ROWID  | T2       |      1 |      1 |      1 |00:00:00.01 |       7 |      1 ||   2 |   NESTED LOOPS                |          |      1 |      1 |      3 |00:00:00.01 |       6 |      1 |

总结：

适合Nested Loop连接的场合：

1 两表关联返回的记录不多。

最理想的情况：驱动表结果集仅返回1条或几条记录，而被驱动表也只匹配了1条或几条记录。即使两个表的数据量巨大，查询速度也是很迅速的。

2.遇到一些不等值查询导致哈希和排序合并连接被限制使用，不得已才使用Nested Loop连接

建立索引的场合：

1.驱动表(t1)的限制条件(n)所在的列建索引(t1_n)

2.被驱动表(t2)的连接条件(t1_id)所在的列建索引(t2_t1_id)

0 0