ORACLE hash join首先选择一个表采用hash结构放入内存，这个表我们通常称为被探测表，然后读取第个表，计算关联列的hash值，看是否能够找到关联到记录。通常应该选用小表作为被探测表优先放入内存中。
下面看这个例子：
SQL> select count(*) from endss.en_ppc_effect_sumdt0 where yyyymmdd = trunc(sysdate-2) ;
COUNT(*)
----------
191430
Elapsed: 00:00:02.27
SQL> select count(*) from enlog.session_fatdt0 where yyyymmdd = trunc(sysdate-2) ;
COUNT(*)
----------
1913790
下面这个SQL在一分钟就执行完成。
SQL> SELECT /*+ordered use_hash(a b c d e) parallel(b 4)*/trunc(sysdate-2),
2 c.modifier AS account,d.top_category AS campaign,a.product_name as adgroup,a.ppc_keyword AS keyword,e.match_type,
3 SUM(a.session_cnt),COUNT(DISTINCT b.cookie_id),SUM(a.sap_cnt),SUM(a.pv),SUM(a.nsnc_new_fb_cnt),SUM(a.os_atm_inquiry_cnt),SUM(a.os_inquiry_cnt),
4 COUNT(DISTINCT a.active_buyer),COUNT(DISTINCT a.os_atm_inquiry_sender),COUNT(DISTINCT a.os_inquiry_sender),'DAILY'
5 FROM enlog.ppc_modifier_dimt0 c,
6 enlog.ppc_top_cate_dimt0 d,
7 enlog.ppc_match_type_dimt0 e,
8 endss.en_ppc_effect_sumdt0 a,
9 enlog.session_fatdt0 b
10 WHERE a.step = 1
11 AND a.yyyymmdd = b.yyyymmdd
12 AND a.session_id = b.session_id
13 AND a.put_in_modifier_id = c.modifier_id
14 AND a.top_category_id = d.top_category_id
15 AND a.match_type_id = e.match_type_id
16 AND a.yyyymmdd = trunc(sysdate-2)
17 GROUP BY c.modifier,d.top_category,a.product_name,a.ppc_keyword,e.match_type;
39394 rows selected.
Elapsed: 00:01:09.17
Execution Plan
----------------------------------------------------------
Plan hash value: 4192880306
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 690 | 1956K (1)| 09:46:50 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | 1 | 690 | 1956K (1)| 09:46:50 | | | Q1,03 | P->S | QC (RAND) |
| 3 | SORT GROUP BY | | 1 | 690 | 1956K (1)| 09:46:50 | | | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | 1 | 690 | 1956K (1)| 09:46:50 | | | Q1,03 | PCWP | |
| 5 | PX SEND HASH | :TQ10002 | 1 | 690 | 1956K (1)| 09:46:50 | | | Q1,02 | P->P | HASH |
|* 6 | HASH JOIN BUFFERED | | 1 | 690 | 1956K (1)| 09:46:50 | | | Q1,02 | PCWP | |
| 7 | BUFFER SORT | | | | | | | | Q1,02 | PCWC | |
| 8 | PX RECEIVE | | 1 | 616 | 1956K (1)| 09:46:50 | | | Q1,02 | PCWP | |
| 9 | PX SEND HASH | :TQ10000 | 1 | 616 | 1956K (1)| 09:46:50 | | | | S->P | HASH |
|* 10 | HASH JOIN | | 1 | 616 | 1956K (1)| 09:46:50 | | | | | |
| 11 | PARTITION RANGE SINGLE | | 1 | 554 | 2 (0)| 00:00:01 | KEY | KEY | | | |
|* 12 | TABLE ACCESS FULL | EN_PPC_EFFECT_SUMDT0 | 1 | 554 | 2 (0)| 00:00:01 | KEY | KEY | | | |
| 13 | MERGE JOIN CARTESIAN | | 24M| 1459M| 1955K (1)| 09:46:48 | | | | | |
| 14 | MERGE JOIN CARTESIAN | | 601K| 27M| 7645 (1)| 00:02:18 | | | | | |
| 15 | VIEW | index$_join$_001 | 199 | 5373 | 3 (0)| 00:00:01 | | | | | |
|* 16 | HASH JOIN | | | | | | | | | | |
| 17 | INDEX FAST FULL SCAN| IDX_PPC_MODIFIER_DIMT0 | 199 | 5373 | 1 (0)| 00:00:01 | | | | | |
| 18 | INDEX FAST FULL SCAN| PPC_MODIFIER_PK | 199 | 5373 | 1 (0)| 00:00:01 | | | | | |
| 19 | BUFFER SORT | | 3025 | 63525 | 7642 (1)| 00:02:18 | | | | | |
| 20 | TABLE ACCESS FULL | PPC_TOP_CATE_DIMT0 | 3025 | 63525 | 38 (0)| 00:00:01 | | | | | |
| 21 | BUFFER SORT | | 41 | 574 | 1955K (1)| 09:46:47 | | | | | |
| 22 | TABLE ACCESS FULL | PPC_MATCH_TYPE_DIMT0 | 41 | 574 | 3 (0)| 00:00:01 | | | | | |
| 23 | PX RECEIVE | | 1 | 74 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 24 | PX SEND HASH | :TQ10001 | 1 | 74 | 2 (0)| 00:00:01 | | | Q1,01 | P->P | HASH |
| 25 | PX BLOCK ITERATOR | | 1 | 74 | 2 (0)| 00:00:01 | KEY | KEY | Q1,01 | PCWC | |
|* 26 | TABLE ACCESS FULL | SESSION_FATDT0 | 1 | 74 | 2 (0)| 00:00:01 | KEY | KEY | Q1,01 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("A"."YYYYMMDD"="B"."YYYYMMDD" AND "A"."SESSION_ID"="B"."SESSION_ID")
10 - access("A"."PUT_IN_MODIFIER_ID"="C"."MODIFIER_ID" AND "A"."TOP_CATEGORY_ID"="D"."TOP_CATEGORY_ID" AND
"A"."MATCH_TYPE_ID"="E"."MATCH_TYPE_ID")
12 - filter("A"."STEP"=1 AND "A"."YYYYMMDD"=TRUNC(SYSDATE@!-2))
16 - access(ROWID=ROWID)
26 - filter("B"."YYYYMMDD"=TRUNC(SYSDATE@!-2))
Statistics
----------------------------------------------------------
53 recursive calls
5 db block gets
10695 consistent gets
9322 physical reads
16024 redo size
2357194 bytes sent via SQL*Net to client
29374 bytes received via SQL*Net from client
2628 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
39394 rows processed
在我调换了成这样后，这个sql在半小时都没有执行完成。
SELECT /*+ordered use_hash(a b c d e) parallel(b 4)*/trunc(sysdate-2),
c.modifier AS account,d.top_category AS campaign,a.product_name as adgroup,a.ppc_keyword AS keyword,e.match_type,
SUM(a.session_cnt),COUNT(DISTINCT b.cookie_id),SUM(a.sap_cnt),SUM(a.pv),SUM(a.nsnc_new_fb_cnt),SUM(a.os_atm_inquiry_cnt),SUM(a.os_inquiry_cnt),
COUNT(DISTINCT a.active_buyer),COUNT(DISTINCT a.os_atm_inquiry_sender),COUNT(DISTINCT a.os_inquiry_sender),'DAILY'
FROM enlog.ppc_modifier_dimt0 c,
enlog.ppc_top_cate_dimt0 d,
enlog.ppc_match_type_dimt0 e,
enlog.session_fatdt0 b,
endss.en_ppc_effect_sumdt0 a
WHERE a.step = 1
AND a.yyyymmdd = b.yyyymmdd
AND a.session_id = b.session_id
AND a.put_in_modifier_id = c.modifier_id
AND a.top_category_id = d.top_category_id
AND a.match_type_id = e.match_type_id
AND a.yyyymmdd = trunc(sysdate-2)
GROUP BY c.modifier,d.top_category,a.product_name,a.ppc_keyword,e.match_type;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/77270/viewspace-620240/ ，如需转载，请注明出处，否则将追究法律责任。
