今天下午，群里面有人讨论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会用小表作为驱动表