Hash Join 一定是选择小表作为驱动表吗
来源：互联网 发布：移动大数据平台叫什么 编辑：程序博客网 时间：2024/04/27 17:22
今天下午，群里面有人讨论HASH JOIN，选择驱动表的问题，我回答是 选择返回结果集小的表作为驱动表，而有些同志不同意，他们认为Oracle一定会选择小表作为驱动表，为了弄明白我以前是否理解错误，现在实验一把:
SQL> create table t1(id number,name varchar2(100));
表已创建。
SQL> create table t2(id number,job varchar2(100));
表已创建。
SQL> begin
2 for i in 1..100000 loop
3 insert into t1 values(i,'luoluo');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> begin
2 for i in 1..10 loop
3 insert into t2 values(1,'DBA');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> begin
2 for i in 1..10 loop
3 insert into t2 values(2,'DBA');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> begin
2 for i in 1..10 loop
3 insert into t2 values(3,'DBA');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> begin
2 for i in 1..10 loop
3 insert into t2 values(4,'DBA');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> begin
2 for i in 1..10 loop
3 insert into t2 values(5,'DBA');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> begin
2 for i in 1..100 loop
3 insert into t2 values(6,'DBA');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> begin
2 for i in 1..100 loop
3 insert into t2 values(7,'DBA');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> begin
2 for i in 1..100 loop
3 insert into t2 values(8,'DBA');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> analyze table t1 compute statistics;
表已分析。
SQL> analyze table t2 compute statistics;
表已分析。
SQL> select name,job from t1,t2 where t1.id=t2.id and t1.id=1;
已选择10行。
执行计划
----------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44 | 660 | 56 (8)| 00:00:01 |
|* 1 | HASH JOIN | | 44 | 660 | 56 (8)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 10 | 53 (8)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 44 | 220 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="T2"."ID")
2 - filter("T1"."ID"=1)
3 - filter("T2"."ID"=1)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
256 consistent gets
0 physical reads
0 redo size
546 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
从实验可以看出如果对大表加了约束条件，那么大表同样会作为驱动表
SQL> exec dbms_stats.delete_table_stats('robinson','t1');
PL/SQL 过程已成功完成。
SQL> exec dbms_stats.delete_table_stats('robinson','t2');
PL/SQL 过程已成功完成。
SQL> select num_rows from user_tables;
NUM_ROWS
----------
50317
41
SQL> analyze table t1 delete statistics;
表已分析。
SQL> analyze table t2 delete statistics;
表已分析。
SQL> select name,job from t1,t2 where t1.id=t2.id and t1.id=1;
已选择10行。
执行计划
----------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27 | 3510 | 56 (8)| 00:00:01 |
|* 1 | HASH JOIN | | 27 | 3510 | 56 (8)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T1 | 3 | 195 | 53 (8)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 10 | 650 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="T2"."ID")
2 - filter("T1"."ID"=1)
3 - filter("T2"."ID"=1)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
256 consistent gets
0 physical reads
0 redo size
546 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
动态采样也是采用大表作为驱动表
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ROBINSON',
3 tabname => 'T1',
4 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
5 method_opt => 'for all columns size repeat',
6 degree => DBMS_STATS.AUTO_DEGREE,
7 cascade=>TRUE
8 );
9 END;
10 /
PL/SQL 过程已成功完成。
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ROBINSON',
3 tabname => 'T2',
4 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
5 method_opt => 'for all columns size repeat',
6 degree => DBMS_STATS.AUTO_DEGREE,
7 cascade=>TRUE
8 );
9 END;
10 /
PL/SQL 过程已成功完成。
SQL> select name,job from t1,t2 where t1.id=t2.id and t1.id=1;
已选择10行。
执行计划
----------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44 | 792 | 56 (8)| 00:00:01 |
|* 1 | HASH JOIN | | 44 | 792 | 56 (8)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 11 | 53 (8)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 44 | 308 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="T2"."ID")
2 - filter("T1"."ID"=1)
3 - filter("T2"."ID"=1)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
256 consistent gets
0 physical reads
0 redo size
546 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> select name,job from t1,t2 where t1.id=t2.id;
已选择350行。
执行计划
----------------------------------------------------------
Plan hash value: 2959412835
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 57 (9)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 18 | 57 (9)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T2 | 350 | 2450 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 100K| 1074K| 52 (6)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="T2"."ID")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
262 consistent gets
0 physical reads
0 redo size
5167 bytes sent via SQL*Net to client
638 bytes received via SQL*Net from client
25 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
350 rows processed
如果不对大表加上任何约束条件，Oracle会用小表作为驱动表
- Hash Join 一定是选择小表作为驱动表吗
- hash join驱动表问题
- SWAP_JOIN_INPUTS Oracle Hint(处理hash join强制大表（segment_size大）作为被驱动表)
- 为何nested loop要求小表驱动，hash join又为何要求小表hashed？
- 并行HASH JOIN小表广播问题
- 为何nested loop要求小表驱动，hash join又为何要求小表hashed？（转）
- OLAP 大表和小表并行hash join
- hash join 驱动表和被驱动表不一定是全表扫描
- 表连接总结--Hash Join
- oracle hash join和nested loop下的驱动表相关测试
- oracle的表连接hash join、nested loop join
- oracle: 表连接方式---hash join原理
- 错误的选择了HASH JOIN！
- 普通表的Join 三种算法(join 一) 嵌套循环Join(Nested Loops Join)、排序合并Join(Sort-Merge Join)和哈希Join(Hash Join)
- Nested Loops Join、Hash join、Merge Sort Join三大经典表连接浅谈（笔记）
- LEFT JOIN 多表选择数据（任意join）
- 驱动表选择
- 分数一定是有理数吗？
- 循环中产生伪随机数
- 再也不要吃方便面了
- Windows Web Server 2003,2008或者64位操作系统安装sqlserver2000企业版的解决方案
- VC++“气球式”工具提示
- Linux根文件系统结构再认识
- Hash Join 一定是选择小表作为驱动表吗
- 笔试SQL语句——学习笔记
- 关于fatal error C1189: #error : WINDOWS.H already included. MFC apps must not #include
- 所言
- VC中SetTimer用法
- API 5CT
- using的几种用法
- 我的程序员之路
- 其沃尔沃确认
