hash join的驱动表依赖统计信息

最新推荐文章于 2024-08-19 23:18:25 发布

cotchte0421

最新推荐文章于 2024-08-19 23:18:25 发布

阅读量79

点赞数

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

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 |
--------------------------------------------------------------------------------

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-682276/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-682276/