nested loop join 、hash join 问题

lioulb 2007-01-12 05:01:50
nested loop join 、hash join
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")

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")

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")

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")

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")

Note
-----
- dynamic sampling used for this statement

---------------------------------------------------------问题如下:
无论T1、T2是否有索引,连接查询时都走hash join
如果对T1加了主键,就会走 nested loop join
为何加了主键就会走nested loop join呢?而没加时都走hash join呢?

环境:
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
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
...全文
865 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
tianhxk 2011-11-27
  • 打赏
  • 举报
回复
07年的帖子?。。。。
bjwang112006 2011-11-27
  • 打赏
  • 举报
回复
NESTED LOOP

对于被连接的数据子集较小的情况,nested loop连接是个较好的选择。nested loop就是扫描一个表,每读到一条记录,就根据索引去另一个表里面查找,没有索引一般就不会是 nested loops。
一般在nested loop中, 驱动表满足条件结果集不大,被驱动表的连接字段要有索引,这样就走nstedloop。如果驱动表返回记录太多,就不适合nested loops了。如果连接字段没有索引,则适合走hash join,因为不需要索引。
可用ordered提示来改变CBO默认的驱动表,可用USE_NL(table_name1 table_name2)提示来强制使用nested loop。

HASH JOIN

hash join是CBO 做大数据集连接时的常用方式。优化器扫描小表(或数据源),利用连接键(也就是根据连接字段计算hash 值)在内存中建立hash表,然后扫描大表,每读到一条记录就来探测hash表一次,找出与hash表匹配的行。
当小表可以全部放入内存中,其成本接近全表扫描两个表的成本之和。如果表很大不能完全放入内存,这时优化器会将它分割成若干不同的分区,不能放入内存的部分就把该分区写入磁盘的临时段,此时要有较大的临时段从而尽量提高I/O 的性能。临时段中的分区都需要换进内存做hash join。这时候成本接近于全表扫描小表+分区数*全表扫描大表的代价和。
至于两个表都进行分区,其好处是可以使用parallel query,就是多个进程同时对不同的分区进行join,然后再合并。但是复杂。
使用hash join时,HASH_AREA_SIZE初始化参数必须足够的大,如果是9i,Oracle建议使用SQL工作区自动管理,设置WORKAREA_SIZE_POLICY 为AUTO,然后调整PGA_AGGREGATE_TARGET即可。
以下条件下hash join可能有优势:
两个巨大的表之间的连接。
在一个巨大的表和一个小表之间的连接。
可用ordered提示来改变CBO默认的驱动表,可用USE_HASH(table_name1 table_name2)提示来强制使用hash join。
蝈蝈俊 2008-01-23
  • 打赏
  • 举报
回复
test
蝈蝈俊 2008-01-23
  • 打赏
  • 举报
回复
我也想知道答案, 顶
lioulb 2007-01-13
  • 打赏
  • 举报
回复
自己顶

17,078

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧