hash join 和普通index 访问的异同

caoliwei

but another question is been asked

how the hash table are organised

caoliwei

confused

Weeping Willow

hash join 时 oracle访问一张表，在内存中建立键的hash表，然后扫描连接中的其他表，根据hash表检测匹配的记录。

相关的参数：
hash_join_enabled=true
PGA_AGGREGATE_TARGET设得足够大（HASH_AREA_SIZE向下兼容）

caoliwei

Hashing is so cool. It’s lightening fast and requires only a small amount of memory
as opposed to a large amount of disk space (think index) or CPU time (think cost
based optimization). There is basically a simple calculation, a jump to a memory
location, and then hopefully a short in-memory sequential search. When the
sequential search is short, the result is an incredibly fast search...much faster than
using any kind of index or a cost based approach.
Hashing consists of only a few components; a hash function, hash buckets, and hash
chains. I’ll take a minute to describe each of these.

caoliwei

Hashing algorithm   is in the  so many oracle compents

caoliwei

so important

caoliwei

http://www.dba-oracle.com/demo/oracle_hashing.htm

caoliwei

just as the data structure of hash table

eagle_fan

最初由 caoliwei 发布
[B]but another question is been asked

how the hash table are organised [/B]

不知道你具体想问什么

举个简单的例子

table T1: column A values: A, B , C
table T2: column B values:A, E, F , C , M．．．．

有查询
select T2.B from T1,T2 where T1.A=T2.B

Hash join下选择table A来建立ｈａｓｈ　ｔａｂｌｅ

ｈａｓｈ函数Ｆｎ(x) = i

Fn(A)=1
Fn(B)=2
Fn(C)=3

所以依照ｔａｂｌｅ　Ｔ１建立的ｈａｓｈ　ｔａｂｌｅ里面就有１，２，３三个值

这时读取table T2中的数据，经过同样的ｈａｓｈ函数Ｆｎ（ｘ）＝ｉ运算

Fn(A)=1命中
Fn(E)=5不能命中
Fn(F)=6不能命中
Fn(C)=3命中
．．．．．