驱动分区oracle and postgresql join method
任务之余抽点时间出来写写博文,希望对新接触的朋友有帮助。今天在这里和大家一起学习一下驱动分区
先绍介oracle的几种多表接连式方:
NESTED LOOP:嵌套循环,其实就是双FOR循环
1.这里要分为驱动表(外部表)和找查表(外部表)
2.找查表要有索引,可以用过索引找查匹配,高提效率,
3.将小的表作为驱动表
4.两表的数据量不大
有些时候oracle优化器选定的驱动表和找查表其实不适合,所以这里须要我们来指定找查表和驱动表,通过加添hint ,USE_NL提示,但是其实不是老是有效,可以应用ordered use_nl(tab1 tab2)强制tab1为驱动表。注意,这里提到的有所环境都是CBO。
所以NESTED LOOP一般用在接连表中有索引,而且索引选择性较好。这里的外部理原就是选定一个驱动表(outer),一个找查表(inner),驱动表的每行都与找查表的记载匹配对核.驱动表的记载越少,则返回的结果集越快。
cost=outer access cost+(inner access cost*outer cardinality)
![驱动和分区](http://img1.ph.126.net/NdrZ2DHdnfahSdC9bEgBTg==/667377169798722001.jpg)
根据公式得出cost=3+(1*14)=17
HASH JOIN:哈希接连用于接连大的数据集,或者大表和小表的关联,优化器应用小表的接连字段在内存中立建哈希表,随后扫描大表,计算出大表接连字段的哈希值,断判否能在哈希表列中找到,功成返回数据,否则抛弃失落。如果表很大的情况下,没法全完放入到内存,那么优化器将会将它分为多少分区,不能放入内存的,那么就写入盘磁的临时段,所以此时要有较大的临时段来高提IO能性。
1.根据表的小大在内存中立建哈希表,然后对哈希表停止探测。
2.小较的表作为散表列,基于接连键立建哈希
3.CBO下任务
4.哈希表最好够能全完放入HASH_AREA_SIZE声明的内存中。
![驱动和分区](http://img6.ph.126.net/set41wlIaBAPftzcdVxXOg==/2517230716741379806.jpg)
上图是最想理的情况下,现实环境中并非那么想理。解释一下图,首先在small data set滤过不必要的数据,生成hash表,也就是build hash,放入hash_area中,然后扫描big data set对行的简直做哈希运算然后到hash area中探测生成的哈希表,这里有bucket,bucket里有哈希表列,多不解释了,扫描表列 ,匹配功成返回数据,否则抛弃。
![驱动和分区](http://img2.ph.126.net/YzLSJEXaYa1xOxJjl4ab3g==/2573807187060109092.jpg)
上图是onepass的情况,
当hash area没法纳容有所的分区,但是却充足纳容最少一个分区时,这类情况就是onepass hash join。须要首先对hash table做分区,然后将其写入到盘磁的临时间空,当hash table实现分区后,然后对probe table也用一样的hash函数作分区,然后对应的分区别分做join。须要注意的是,除了第一个分区,从第二个分区开始,Oracle会动自根据分区的小大来换交build table和probe table,以期望到达更好的能性。因为有所的分区只须要从盘磁上取读一次以可就实现,所以叫onepass.
1.扫描第二张表,对join键做hash运算,定确好对应的partition和bucket
2.查看bitmap,定确bucket是不是有数据,没有则直接抛弃
3.如果有数据,并且这个partition是在内存中的,就进入对应的桶去确精匹配,能匹配上,就返回这行数据,否则抛弃
4.如果partition是在盘磁上的,则将这行数据放入盘磁中暂存起来,存保的情势也是partition,bucket的式方
5.当第二张表被扫描完后,剩下的是驱动表和探测表生成的一大堆partition,保留在盘磁上
6.由于边两的数据都按照雷同的hash算法做了partition和bucket,当初只要成对的比拟边两partition数据便可,并且在比拟的时候,oracle也做了优化处置,没有严厉的驱动与被驱动系关,他会在partition对当选小较的一个作为驱动来停止,直到盘磁上有所的partition对都join完
multipass
最差的hash join,此时hash area小到连一个partition也纳容不下,当扫描好驱动表后,可能只有半个partition留在hash area中,另半个加其他的partition全在盘磁上,剩下的骤步和onepass比价似类,不同的是针对partition的处置
由于驱动表只有半个partition在内存中,探测表对应的partition数据做探测时,如果匹配不上,这行还不能直接抛弃,须要续继保留到盘磁,和驱动表剩下的半个partition再做join,这里举例的是内存可以装下半个partition,如果装的更少的话,复反join的数次将更多,当产生multipass时,partition物理读的数次会著显加增。
cost = (outer access cost * # of hash partitions) + inner access cost
SORT MERGE JOIN
两表行源停止序排,这里没有驱动表和找查表,由于边两都经已序排,所以直接可以通过接连件条停止结果定确。
hash join着重消费CPU,而sort merge join着重消费IO,可以通过USE_MERGE(TAB1,TAB2)强制应用
触发则规:
RBO模式,不等价接连(>,>+,<,<=)
hash_join_enabled=false
数据源经已序排
cost的算法和hash join一样。
postgresql 接连式方:
和oracle一样,供给了三种接连式方,nested loop ,hash_join,merge join
NESTED LOOP:
举例:
SELECT oid
FROM pg_proc
ORDER BY 1
LIMIT 8;
CREATE TEMPORARY TABLE sample1 (id, junk) AS
SELECT oid, repeat('x', 250)
FROM pg_proc
ORDER BY random(); -- add rows in random order
CREATE TEMPORARY TABLE sample2 (id, junk) AS
SELECT oid, repeat('x', 250)
FROM pg_class
ORDER BY random(); -- add rows in random order
建创的这些表没有索引和统计息信
EXPLAIN SELECT sample2.junk
FROM sample1 JOIN sample2 ON (sample1.id = sample2.id)
WHERE sample1.id = 33;
QUERY PLAN
---------------------------------------------------------------------
Nested Loop (cost=0.00..253.42 rows=378 width=32)
-> Seq Scan on sample1 (cost=0.00..220.76 rows=54 width=4)
Filter: (id = 33::oid)
-> Materialize (cost=0.00..27.95 rows=7 width=36)
-> Seq Scan on sample2 (cost=0.00..27.91 rows=7 width=36)
Filter: (id = 33::oid)
(6 rows)
嵌套接连序顺扫描源码:
for (i = 0; i < length(outer); i++)
for (j = 0; j < length(inner); j++)
if (outer[i] == inner[j])
output(outer[i], inner[j]);
这里和oracle 的理原是一样的,就不再多解释。
HASH JOIN:
EXPLAIN SELECT sample1.junk
FROM sample1 JOIN sample2 ON (sample1.id = sample2.id)
WHERE sample2.id > 33;
QUERY PLAN
----------------------------------------------------------------------
Hash Join (cost=33.55..1097.55 rows=24131 width=32)
Hash Cond: (sample1.id = sample2.id)
-> Seq Scan on sample1 (cost=0.00..194.01 rows=10701 width=36)
-> Hash (cost=27.91..27.91 rows=451 width=4)
-> Seq Scan on sample2 (cost=0.00..27.91 rows=451 width=4)
Filter: (id > 33::oid)
HASH JOIN源码:
for (j = 0; j < length(inner); j++)
hash_key = hash(inner[j]);
append(hash_store[hash_key], inner[j]);
for (i = 0; i < length(outer); i++)
hash_key = hash(outer[i]);
for (j = 0; j < length(hash_store[hash_key]); j++)
if (outer[i] == hash_store[hash_key][j])
output(outer[i], inner[j]);
merge join:
EXPLAIN SELECT sample1.junk
FROM sample1 JOIN sample2 ON (sample1.id = sample2.id);
QUERY PLAN
--------------------------------------------------------------------------
Merge Join (cost=1005.10..2097.74 rows=72392 width=32)
Merge Cond: (sample2.id = sample1.id)
-> Sort (cost=94.90..98.28 rows=1353 width=4)
Sort Key: sample2.id
-> Seq Scan on sample2 (cost=0.00..24.53 rows=1353 width=4)
-> Sort (cost=910.20..936.95 rows=10701 width=36)
Sort Key: sample1.id
-> Seq Scan on sample1 (cost=0.00..194.01 rows=10701 width=36)
这里两表位置变更无影响。
merge join源码:
sort(outer);
sort(inner);
i = 0;
j = 0;
save_j = 0;
while (i < length(outer))
if (outer[i] == inner[j])
output(outer[i], inner[j]);
if (outer[i] <= inner[j] && j < length(inner))
j++;
if (outer[i] < inner[j])
save_j = j;
else
i++;
j = save_j;
ANALYZE sample1;
ANALYZE sample2;
有了统计息信后:
postgres=# EXPLAIN SELECT sample2.junk
postgres-# FROM sample1 JOIN sample2 ON (sample1.id = sample2.id);
QUERY PLAN
------------------------------------------------------------------------
Hash Join (cost=17.39..139.45 rows=284 width=254)
Hash Cond: (sample1.id = sample2.id)
-> Seq Scan on sample1 (cost=0.00..110.43 rows=2343 width=4)
-> Hash (cost=13.84..13.84 rows=284 width=258)
-> Seq Scan on sample2 (cost=0.00..13.84 rows=284 width=258)
CREATE INDEX i_sample1 on sample1 (id);
CREATE INDEX i_sample2 on sample2 (id);
立建关相索引后:
postgres=# EXPLAIN SELECT sample2.junk
postgres-# FROM sample1 JOIN sample2 ON (sample1.id = sample2.id)
postgres-# WHERE sample1.id = 33;
QUERY PLAN
---------------------------------------------------------------------------------
Nested Loop (cost=0.00..16.55 rows=1 width=254)
-> Index Scan using i_sample1 on sample1 (cost=0.00..8.27 rows=1 width=4)
Index Cond: (id = 33::oid)
-> Index Scan using i_sample2 on sample2 (cost=0.00..8.27 rows=1 width=258)
Index Cond: (id = 33::oid)
不再是Inner Sequential Scan,而是Inner Index Scan
文章结束给大家分享下程序员的一些笑话语录:
Bphone之你们聊,我先走了!移动说:我在phone前加o,我叫o缝;苹果说:我在phone前i,我是i缝;微软说:我在phone前加w,我叫w缝;三星说:你们聊,我先走了!
将来王建宙写回忆录的时候,一定要有一句“常小兵为中国移动的发展做出了不可磨灭的贡献”。