hash join的驱动表依赖统计信息，主要是之前听到一位同事说，驱动表依赖的是段头块的信息来判断表大小，做了下实验，不是。

SQL> create table wxh_tbd1 as select * from dba_objects where rownum<1000;

表已创建。

SQL> create table wxh_tbd2 as select * from dba_objects;

表已创建。

SQL> exec dbms_stats.gather_table_stats(user,'wxh_tbd1');

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.gather_table_stats(user,'wxh_tbd2');

PL/SQL 过程已成功完成。

SQL> select count(*) from wxh_tbd1 a ,wxh_tbd2 b where a.object_id=b.object_id;

执行计划
----------------------------------------------------------
Plan hash value: 2233783557

--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |     9 |   164   (3)| 00:00:02 |
|   1 |  SORT AGGREGATE     |          |     1 |     9 |            |          |
|*  2 |   HASH JOIN         |          |   999 |  8991 |   164   (3)| 00:00:02 |
|   3 |    TABLE ACCESS FULL| WXH_TBD1 |   999 |  3996 |     5   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| WXH_TBD2 | 49759 |   242K|   158   (2)| 00:00:02 |
--------------------------------------------------------------------------------

SQL> insert into wxh_tbd1 select * from dba_objects;

已创建49760行。

SQL> select count(*) from wxh_tbd1 a ,wxh_tbd2 b where a.object_id=b.object_id;

执行计划
----------------------------------------------------------
Plan hash value: 2233783557

--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |     9 |   164   (3)| 00:00:02 |
|   1 |  SORT AGGREGATE     |          |     1 |     9 |            |          |
|*  2 |   HASH JOIN         |          |   999 |  8991 |   164   (3)| 00:00:02 |
|   3 |    TABLE ACCESS FULL| WXH_TBD1 |   999 |  3996 |     5   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| WXH_TBD2 | 49759 |   242K|   158   (2)| 00:00:02 |
--------------------------------------------------------------------------------

SQL> exec dbms_stats.gather_table_stats(user,'wxh_tbd1');

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.gather_table_stats(user,'wxh_tbd2');

PL/SQL 过程已成功完成。

SQL> select count(*) from wxh_tbd1 a ,wxh_tbd2 b where a.object_id=b.object_id;

执行计划
----------------------------------------------------------
Plan hash value: 2033256435

--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |    10 |   470   (3)| 00:00:06 |
|   1 |  SORT AGGREGATE     |          |     1 |    10 |            |          |
|*  2 |   HASH JOIN         |          |   100K|   981K|   470   (3)| 00:00:06 |
|   3 |    TABLE ACCESS FULL| WXH_TBD2 | 49763 |   242K|   158   (2)| 00:00:02 |
|   4 |    TABLE ACCESS FULL| WXH_TBD1 |   100K|   490K|   309   (2)| 00:00:04 |
--------------------------------------------------------------------------------