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(*)
----------
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