nested loop join 、hash join 问题

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(*)
----------
704SQL> select count(*) from t2;COUNT(*)
----------
812SQL> 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

解决方案 »

  1.   

    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。