对Hash Join的一次优化

cyt2005

不错学习了

BTxigua

为什么在bigtable上不建立索引呢？

cyt2005

原帖由 rollingpig 于 2008-3-17 13:24 发表
这样如何？

select /*+ leading(b) use_hash(a b) */ distinct a.ID
from BIG_TABLE a, SMALL_TABLE b
where (a.category||' '||a.site_id  = b.from_cat||' '||b.site_id
   or a.category2||' '||a.site_id  = b.from_cat||' '||b.site_id
) and a.sale_end >= sysdate;

lz这个sql使用了么？结果如何?

netbanker

good article, and 好熟悉的table name

paulyibinyi

顶 学习下

eagle_fan

原帖由 gengyonghui 于 2008-3-17 13:08 发表
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;


两点建议：

1.查看sale_end列，看是否有必要建索引。
2.改写成
select a.ID
from BIG_TABLE a
where a.sale_end >= sysdate
and exists(select null
             from  SMALL_TABLE b
            where a.site_id  = b.site_id
              and b.from_cat in(a.category,a.category2)
           )

忘记说明了，sale_end >= sysdate返回大量的数据，不适合走索引，自然也不适合走Nest Loop

[ 本帖最后由 eagle_fan 于 2008-3-17 14:37 编辑 ]

eagle_fan

原帖由 netbanker 于 2008-3-17 14:04 发表
good article, and 好熟悉的table name

原来的表名被我匿了，取了个通用的表名

d.c.b.a

从Access & Filter这儿看不出东东?

eagle_fan

原帖由 rollingpig 于 2008-3-17 13:24 发表
这样如何？

select /*+ leading(b) use_hash(a b) */ distinct a.ID
from BIG_TABLE a, SMALL_TABLE b
where (a.category||' '||a.site_id  = b.from_cat||' '||b.site_id
   or a.category2||' '||a.site_id  = b.from_cat||' '||b.site_id
) and a.sale_end >= sysdate;

这是你的SQL的PLAN,个人感觉上没有用Union自然
[PHP]
--------------------------------------------------------------------------
| Id  | Operation            |  Name        | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |    81 |  7047 |    34  (12)|
|   1 |  SORT UNIQUE         |              |    81 |  7047 |    34  (12)|
|   2 |   CONCATENATION      |              |       |       |            |
|*  3 |    HASH JOIN         |              |     4 |   348 |    17  (12)|
|   4 |     TABLE ACCESS FULL| SMALL_TABLE  |  1879 | 48854 |    14   (8)|
|*  5 |     TABLE ACCESS FULL| BIG_TABLE    |     4 |   244 |     3  (34)|
|*  6 |    HASH JOIN         |              |     4 |   348 |    17  (12)|
|   7 |     TABLE ACCESS FULL| SMALL_TABLE  |  1879 | 48854 |    14   (8)|
|*  8 |     TABLE ACCESS FULL| BIG_TABLE    |     4 |   244 |     3  (34)|
--------------------------------------------------------------------------
[/PHP]

我们在tuning sql的时候可以站在oracle的角度想问题

起初我认为oracle应该更加smart一点，可以通过small_table(from_cat,site_id)来建立build table，然后在probe big_table的时候计算出两个hash值，第一个值基于(category, site_id)，第二个值基于(category2,site_id)然后根据这两个hash值选择相应的bucket做链表扫描，这样的话FTS只需要一次

可惜oracle让我失望了，并没有如我一般的smart ，所以两次FTS无法避免

kkngisn

原帖由 eagle_fan 于 2008-3-17 11:47 发表


仔细看，在一个bucket中最多的行数竟然有2531行，因为bucket中是一个链表的结构，所以这几千行都是串在一个链表上。
由这一点想到这个Hash Table所依赖的hash key的distinct value可能太少，重复值太多。否则不应该会有这么多行在同一个bucket里面。

http://www.itpub.net/thread-955276-1-1.html


  隐约记得数据块被放到BUFFER的地址中是根据数据块的位置然后做一个什么MOD的动作