转: http://www.itpub.net/viewthread.php?tid=955209&extra=&page=1
对Hash Join的一次优化
[@more@]对Hash Join的一次优化前两天解决了一个优化SQL的case，SQL语句如下，big_table为150G大小，small_table很小，9000多条记录，不到1M大小
hash_area_size, sort_area_size均设置足够大，可以进行optimal hash join和memory sort
select /*+ leading(b) use_hash(a b) */ distinct a.ID
from BIG_TABLE a, SMALL_TABLE b
where (a.category = b.from_cat or
a.category2 = b.from_cat) and
a.site_id = b.site_id and
a.sale_end >= sysdate;
hash_area_size, sort_area_size均设置足够大，可以进行optimal hash join和memory sort
select /*+ leading(b) use_hash(a b) */ distinct a.ID
from BIG_TABLE a, SMALL_TABLE b
where (a.category = b.from_cat or
a.category2 = b.from_cat) and
a.site_id = b.site_id and
a.sale_end >= sysdate;
PHP code:
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 174 | 18 (17)|
| 1 | SORT UNIQUE | | 2 | 174 | 18 (17)|
|* 2 | HASH JOIN | | 2 | 174 | 17 (12)|
| 3 | TABLE ACCESS FULL | SMALL_TABLE | 1879 | 48854 | 14 (8)|
|* 4 | TABLE ACCESS FULL | BIG_TABLE | 4 | 244 | 3 (34)|
--------------------------------------------------------------------------