nested loop join 、hash join

显示全部楼层 · 2009-1-4 14:52:28

SQL> create table t1 as select * from user_tables;
表已创建。
SQL> create table t2 as select * from user_indexes;
表已创建。
SQL> select count(*) from t1;
COUNT(*)
----------
704
SQL> select count(*) from t2;
COUNT(*)
----------
812
SQL> set autot on exp
-----------------------------------T1 T2均无索引
SQL> select count(*) from t1,t2 where t1.table_name = t2.table_name;
COUNT(*)
----------
789

执行计划
----------------------------------------------------------
Plan hash value: 906334482
----------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT|| 1 |34 |14 (8)| 00:00:01 |
| 1 |SORT AGGREGATE || 1 |34 |
|
|
|*2 | HASH JOIN || 812 | 27608 |14 (8)| 00:00:01 |
| 3 |TABLE ACCESS FULL| T1 | 704 | 11968 | 6 (0)| 00:00:01 |
| 4 |TABLE ACCESS FULL| T2 | 812 | 13804 | 7 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."TABLE_NAME"="T2"."TABLE_NAME&quot

Note
-----
- dynamic sampling used for this statement
--------------这里走的是hash join 方式
---------------------------------对T1建立索引
SQL> create index index_tn_t1 on t1(table_name);
索引已创建。
SQL> select count(*) from t1,t2 where t1.table_name = t2.table_name;
COUNT(*)
----------
789

执行计划
----------------------------------------------------------
Plan hash value: 2970951385
--------------------------------------------------------------------------------
------
| Id| Operation
| Name| Rows| Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT |
| 1 |34 |11(10)| 00:0
0:01 |
| 1 |SORT AGGREGATE|
| 1 |34 |
|
|
|*2 | HASH JOIN
|
| 812 | 27608 |11(10)| 00:0
0:01 |
| 3 |INDEX FAST FULL SCAN| INDEX_TN_T1 | 704 | 11968 | 3 (0)| 00:0
0:01 |
| 4 |TABLE ACCESS FULL | T2
| 812 | 13804 | 7 (0)| 00:0
0:01 |
--------------------------------------------------------------------------------
------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."TABLE_NAME"="T2"."TABLE_NAME&quot

Note
-----
- dynamic sampling used for this statement
--------------------------------对T2建立索引
SQL> create index index_tn_t2 on t2(table_name);
索引已创建。
SQL> select count(*) from t1,t2 where t1.table_name = t2.table_name;
COUNT(*)
----------
789

执行计划
----------------------------------------------------------
Plan hash value: 3001097500
--------------------------------------------------------------------------------
------
| Id| Operation
| Name| Rows| Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT |
| 1 |34 | 7(15)| 00:0
0:01 |
| 1 |SORT AGGREGATE|
| 1 |34 |
|
|
|*2 | HASH JOIN
|
| 812 | 27608 | 7(15)| 00:0
0:01 |
| 3 |INDEX FAST FULL SCAN| INDEX_TN_T1 | 704 | 11968 | 3 (0)| 00:0
0:01 |
| 4 |INDEX FAST FULL SCAN| INDEX_TN_T2 | 812 | 13804 | 3 (0)| 00:0
0:01 |
--------------------------------------------------------------------------------
------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."TABLE_NAME"="T2"."TABLE_NAME&quot

Note
-----
- dynamic sampling used for this statement
------------------------------------删除T1的索引，保留T2的索引
SQL> drop index index_tn_t1;
索引已删除。
SQL> select count(*) from t1,t2 where t1.table_name = t2.table_name;
COUNT(*)
----------
789

执行计划
----------------------------------------------------------
Plan hash value: 3597742774
--------------------------------------------------------------------------------
------
| Id| Operation
| Name| Rows| Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT |
| 1 |34 |10(10)| 00:0
0:01 |
| 1 |SORT AGGREGATE|
| 1 |34 |
|
|
|*2 | HASH JOIN
|
| 812 | 27608 |10(10)| 00:0
0:01 |
| 3 |TABLE ACCESS FULL | T1
| 704 | 11968 | 6 (0)| 00:0
0:01 |
| 4 |INDEX FAST FULL SCAN| INDEX_TN_T2 | 812 | 13804 | 3 (0)| 00:0
0:01 |
--------------------------------------------------------------------------------
------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."TABLE_NAME"="T2"."TABLE_NAME&quot

Note
-----
- dynamic sampling used for this statement
-------------------------------------对T1建立主键
SQL> alter table t1 add constraint pk_t1 primary key (table_name);
表已更改。
SQL> select count(*) from t1,t2 where t1.table_name = t2.table_name;
COUNT(*)
----------
789

执行计划
----------------------------------------------------------
Plan hash value: 2067181848
--------------------------------------------------------------------------------
------
| Id| Operation
| Name| Rows| Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT |
| 1 |34 | 3 (0)| 00:0
0:01 |
| 1 |SORT AGGREGATE|
| 1 |34 |
|
|
| 2 | NESTED LOOPS |
| 812 | 27608 | 3 (0)| 00:0
0:01 |
| 3 |INDEX FAST FULL SCAN| INDEX_TN_T2 | 812 | 13804 | 3 (0)| 00:0
0:01 |
|*4 |INDEX UNIQUE SCAN | PK_T1 | 1 |17 | 0 (0)| 00:0
0:01 |
--------------------------------------------------------------------------------
------

Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."TABLE_NAME"="T2"."TABLE_NAME&quot

Note
-----
- dynamic sampling used for this statement
---------------------------------------------------------问题如下：
无论T1、T2是否有索引，连接查询时都走hash join
如果对T1加了主键，就会走 nested loop join
为何加了主键就会走nested loop join呢？而没加时都走hash join呢？[

千问 · 2009-1-4 14:52:28

补充一下环境：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE10.2.0.1.0Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

千问 · 2009-1-4 14:52:28

自己顶上来

千问 · 2009-1-4 14:52:28

Note
—–
- dynamic sampling used for this statement

千问 · 2009-1-4 14:52:28

oracle是基于成本的

千问 · 2009-1-4 14:52:28

我是这样认为D
因为开始是普通index所以可能存在重复的key吧，你看一个explain的rows是700多一个是800左右。返回记录是700多，那oracle 认为你返回的基本就是记录集里的大部分记录了所以走hash jion
最后个因为你建立了唯一索引，所以oracle知道nest loop join的话out loop来驱动inner loop
而out的row只可能在inner里找到唯一记录（inner是是唯一索引返回的记录集）
所以扫描唯一索引后的rows是1
虽然前面还是800左右并返回700多的记录
row=1让 oracle认为你返回的内容是整个rowset中的小部分所以用了nest loop join

千问 · 2009-1-4 14:52:28

谢谢dancingpig的回答，我试了下，如果对表建立唯一索引的话，就会走nest loop join

千问 · 2009-1-4 14:52:28

问题的测试中，
SQL> alter table t1 add constraint pk_t1 primary key (table_name);
加了主键，同时也加了唯一索引，所以执行计划是NESTED LOOPS;
不过如果T1的记录比较少，加了唯一索引，也会走HASH join,至于加了唯一索引情况下，数据量到多少，才会走NESTED LOOPS，我还不知。

千问 · 2009-1-4 14:52:28

千问 · 2009-1-4 14:52:28

是不是你的'PLAN_TABLE' 表有问题？首先把'PLAN_TABLE' 内的数据全部删除看看，再不行，重建'PLAN_TABLE' 表