nested loops/hash join

最近看执行计划时，老是看到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来手动做动态采样分析？或者写个触发器，隔那么久做一次？