nested loops/hash join
来源:互联网 发布:大数据分析用户行为 编辑:程序博客网 时间:2024/05/19 00:12
最近看执行计划时,老是看到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来手动做动态采样分析?或者写个触发器,隔那么久做一次?
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
- NESTED LOOPS HASH JOIN
- NESTED LOOPS & HASH JOINS & MERGE JOIN
- Nested loops, Hash join and Sort Merge joins – difference?
- Nested Loops Join、Hash join、Merge Sort Join三大经典表连接浅谈(笔记)
- Oracle执行计划中的连接方式nested loops join、sort merge joinn、hash join
- Nested loops、Hash join、Sort merge join(三种连接类型原理、使用要点)
- Nested Loops Join(嵌套连接)
- Nested Loops Join(嵌套连接)
- Nested Loops Join(嵌套连接)
- 普通表的Join 三种算法(join 一) 嵌套循环Join(Nested Loops Join)、排序合并Join(Sort-Merge Join)和哈希Join(Hash Join)
- 认识优化查询中的Merge Join、Nested Loops和Hash Match
- 【学习】认识优化查询中的Merge Join、Nested Loops和Hash Match
- 认识优化查询中的Merge Join、Nested Loops和Hash Match
- 嵌套循环连接(nested loops join)原理
- 嵌套循环连接(nested loops join)原理
- HASH JOIN ,SORT MERGE JOIN ,NESTED LOOP
- HASH JOIN/MERGE JOIN/NESTED LOOP
- 轻量级框架和重量级框架
- 正则表达式(一)——基本知识
- 关于org.hibernate.InvalidMappingException
- ceil 函数
- javascript基础篇 1
- nested loops/hash join
- oracle ORA-00906:缺失左大括号
- 知耻而后勇
- pl/sql条件和顺序控制
- clc 函数
- org.hibernate.LazyInitializationException
- clear 函数
- Configure-step in VLC building
- conj 函数