最近看执行计划时,老是看到nested loops/hash join这些词,于是做了个实验,贴出来如果错误请指出。 ps:顺便也谈了下CBO 1.创建表t1,t2 都只有很少的几行数据,对表t1加个索引 SQL> create table t1(id number, name varchar2(10)); Table created. SQL> create table t2(id number, name varchar2(10)); Table created. SQL> insert into t1 values(1, 'test'); 1 row created. SQL> insert into t1 values(2, 'test'); 1 row created. SQL> insert into t2 values(2, 'test'); 1 row created. SQL> commit; Commit complete. SQL> create index idx_t1 on t1(id); Index created. 打开执行计划跟踪: SQL> set autotrace trace exp; SQL> set linesize 180 SQL> select * from t1, t2 where t1.id=t2.id; Execution Plan ---------------------------------------------------------- Plan hash value: 580336636 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time| -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 40 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 0 |1 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 1 | 40 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | T2 | 1 | 20 | 2 (0)|00:00:01| |* 4 | INDEX RANGE SCAN | IDX_T1 | 1 | | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("T1"."ID"="T2"."ID") Note ----- - dynamic sampling used for this statement(请注意,只有第一次才会自动分析,以后都要自己来手动分析) 因为表t1在连接字段id上加了索引所以对于t1表的扫描是走的索引,而t2是全表扫描。两个表的连接方式是nested loops(等下会说明原因) (2)接着对t2的id上建立一个索引 接着再执行对上面的SQL语句查看执行计划,依然一样。第一感觉是觉得不对,对t2表应该也走索引的,但是仔细一想,因为这个特殊情况t2表里面只有全为id=2的字段,如果还走索引代价反而更高。而在10g中默认的是采用的CBO优化器,所以出现这种情况就很正常了。 (3)接着对表t2插入一些数据。 Declare Begin For I int 2..1000 loop Insert into t2 values(3,’test’);--这里其实应该绑定变量的 End loop; End; 然后insert into t1 values(3, ‘test’); 再执行selec * from t1, t2 where t1.id=t2.id 执行计划依然和上面一样: SQL> select * from t2, t1 where t2.id=t1.id; Execution Plan ---------------------------------------------------------- Plan hash value: 580336636 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time| -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 999 | 15984 | 4 (25)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 8 | 1 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 999 | 15984 | 4 (25)| 00:00:01 | | 3 | TABLE ACCESS FULL | T2 | 999 | 7992 | 2 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | IDX_T1 | 1 | | 0 (0)|00:00:01| -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("T2"."ID"="T1"."ID") 这又是为什么呢?同上面一样,虽然在两个表在连接时在id=2这行记录上使用,对t2使用inde range scan(使用索引)效率会很高,但是对于id=3这行就不同了,因为在t2中存在几乎所有的行都是id=3,CBO做这样的决定是明智的。 接着我将SQL语句改为 SQL> select * from t2, t1 where t2.id=t1.id and t2.id=2;红色部分限制了等下连接时,t2的cardinality值(筛选出来行的占表总行数比)很小,那么使用索引的价值就出来了。于是看到执行计划: Execution Plan ---------------------------------------------------------- Plan hash value: 4102592341 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 16 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 8 | 1 (0)| 00 :00:01 | | 2 | NESTED LOOPS | | 1 | 16 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 8 | 2 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | IDX_T2 | 1 | | 1 (0)| 00 :00:01 | |* 5 | INDEX RANGE SCAN | IDX_T1 | 1 | | 0 (0)| 00 :00:01 | -------------------------------------------------------------------------------- -------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("T2"."ID"=2) 5 - access("T1"."ID"=2) (4)接着对t1也插入大量数据。 Declare Begin For I in 2..1000 loop Insert into t2 values(3,’test’);--这里其实应该绑定变量的 End loop; End; 当然必须先手动执行分析,因为我们已经对表插入了数据。 SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true); PL/SQL procedure successfully completed. SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade=>true); PL/SQL procedure successfully completed. SQL> select * from t1,t2 where t1.id=t2.id; Execution Plan ---------------------------------------------------------- Plan hash value: 2959412835 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 998K| 15M| 22 (82)| 00:00:01 | |* 1 | HASH JOIN | | 998K| 15M| 22 (82)| 00:00:01 | | 2 | TABLE ACCESS FULL| T2 | 999 | 7992 | 2 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| T1 | 1002 | 8016 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T1"."ID"="T2"."ID") 可以看到,此时两个表都是全表扫描了,CBO的功力展现了因为两个表中用索引筛选效果不明显,在连接字段几乎都是一样。同时也应该注意,两个表之间的连接方式是hash join了。 (5)那么什么是hash join/nested loops,什么情况下选择hash join,什么情况选择 nested loops? Hash join:在将内表中的符合条件的行全部筛选出来,然后对连接字段进行hash,得到一个hash系列集,放置内存区域,然后再外表的行的那个连接字段进行hash,如果得到的hash值在之前的那个hash集合里面存在的话,那么这行就是符合条件的。更详细的解答请看这篇文章:http://www.eygle.com/rss/20101128.html Nested loops:就是对于内表符合条件的结果集较少的情况使用,然后在内表每次取出一行,再扫描外表,看是否有符合的。循环下去,知道内表符合条件的每行都遍历了一遍。 那么为什么情况使用它们呢?因为对于hash join来说,它的时间复杂度大致相当于两个for循环(并非嵌套)O(n+m)(n,m表示内外表扫描的时间复杂度,也可以简单的理解为是内外表符合条件的行数,虽然有些不恰当),为什么这么说呢?因为hash join是先把内表的结果集全部算出来,完了之后再对外表做一个全表扫描。所以说是O(n+m)。nested loops,对于内表的符合条件的每行,都会在外表去扫描一下,看是外表否有符合条件的行。这个就相当于两个for循环嵌套了时间复杂度O(n*m)。所以在对于m或是n中有一个很小的情况下(内表的符合条件很少的情况下),那么O(n*m)的时间比O(m+n)的时间可能更少。但是对于m和n都很大的情况下,当然就会选择O(m+n)了。 这就是我自己的一点点心得,如果有什么理解错误的地方请指出。 顺便问个问题: 因为有资料说,只有SQL第一次执行时才做动态采样分析,那么以后如果表里面数据变化了都要我们手动做吗?在应该程序中,不会时间久了就会效率越来越低啊? 难道是过那么一段时间有DBA来手动做动态采样分析?或者写个触发器,隔那么久做一次? |
nested loops/hash join
最新推荐文章于 2020-10-04 15:49:42 发布