驱动分区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)
根据公式得出cost=3+（1*14）=17
HASH JOIN:哈希接连用于接连大的数据集，或者大表和小表的关联，优化器应用小表的接连字段在内存中立建哈希表，随后扫描大表，计算出大表接连字段的哈希值，断判否能在哈希表列中找到，功成返回数据，否则抛弃失落。如果表很大的情况下，没法全完放入到内存，那么优化器将会将它分为多少分区，不能放入内存的，那么就写入盘磁的临时段，所以此时要有较大的临时段来高提IO能性。
1.根据表的小大在内存中立建哈希表，然后对哈希表停止探测。
2.小较的表作为散表列，基于接连键立建哈希
3.CBO下任务
4.哈希表最好够能全完放入HASH_AREA_SIZE声明的内存中。
上图是最想理的情况下，现实环境中并非那么想理。解释一下图，首先在small data set滤过不必要的数据，生成hash表，也就是build hash，放入hash_area中，然后扫描big data set对行的简直做哈希运算然后到hash area中探测生成的哈希表，这里有bucket，bucket里有哈希表列，多不解释了，扫描表列 ，匹配功成返回数据，否则抛弃。
上图是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缝；三星说：你们聊，我先走了！
将来王建宙写回忆录的时候，一定要有一句“常小兵为中国移动的发展做出了不可磨灭的贡献”。
