HASH JOIN 跟NESTED LOOP 快一个列子

jinghuainfo

浏览: 1526427 次
性别:
来自: 北京

最近访客更多访客>>

devcang

u012363178

hslb1985

chienchia

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (1993)

社区版块

存档分类

2012-02 ( 11)
2012-01 ( 15)
2011-12 ( 16)
更多存档...

在报表库的每个小时数据量23113条时候
NESTED LOOP 要跑3分钟而 HASH JOIN 才7秒钟

NESTED LOOP:
SELECT * 
FROM ( SELECT ACTIONID, 
OPCONTENTID, 
MOUDULEID, 
PORTALID, 
'lars01.OP_MAIL_VISIT_04_1105' AS table_name, 
TO_DATE (20110501000000, 'yyyymmddhh24miss') + (9 / 24) 
AS INSERTTIME_START, 
TRUNC (TO_DATE (20110501000000, 'yyyymmddhh24miss')) 
+ ( (9 + 1) / 24) 
AS INSERTTIME_END, 
MIN (RECORDID) AS RECORDID, 
COUNT (1) AS row_num 
FROM lars01.OP_MAIL_VISIT_03_1104 
WHERE inserttime >= 
TO_DATE (20110501000000, 'yyyymmddhh24miss') + (9 / 24) 
AND inserttime < 
TRUNC (TO_DATE (20110501000000, 'yyyymmddhh24miss')) 
+ ( (9 + 1) / 24) 
GROUP BY ACTIONID, 
OPCONTENTID, 
MOUDULEID, 
PORTALID) t 
LEFT JOIN 
(SELECT DISTINCT action_id, 
module_iD, 
portal_id, 
opcontent_id 
FROM etl_rule s 
WHERE rule_valid = 1 
AND rule_type = '点击 ' AND TO_CHAR (action_id) IN (SELECT *
 
FROM TABLE ( 
SELECT f_etl_proc ( 
check_value01 
|| ',' 
|| check_value02) 
FROM etl_condition 
WHERE condition_id = 
3))) s 
ON ( t.actionid = s.action_id 
AND t.opcontentid = s.opcontent_id 
AND t.MOUDULEID = s.module_iD 
AND t.PORTALID = s.portal_id) 
WHERE action_id IS NULL

DBA 说 etl_rule每条记录都要全表扫描 TABLE() 在开发库单独拿出该语句执行要122秒

而HASH JOIN 只要2.04秒 


HASH JOIN:

SELECT * 
FROM ( SELECT ACTIONID, 
OPCONTENTID, 
MOUDULEID, 
PORTALID, 
'lars01.OP_MAIL_VISIT_04_1105' AS table_name, 
TO_DATE (20110501000000, 'yyyymmddhh24miss') + (9 / 24) 
AS INSERTTIME_START, 
TRUNC (TO_DATE (20110501000000, 'yyyymmddhh24miss')) 
+ ( (9 + 1) / 24) 
AS INSERTTIME_END, 
MIN (RECORDID) AS RECORDID, 
COUNT (1) AS row_num 
FROM lars01.OP_MAIL_VISIT_03_1104 
WHERE inserttime >= 
TO_DATE (20110501000000, 'yyyymmddhh24miss') + (9 / 24) 
AND inserttime < 
TRUNC (TO_DATE (20110501000000, 'yyyymmddhh24miss')) 
+ ( (9 + 1) / 24) 
GROUP BY ACTIONID, 
OPCONTENTID, 
MOUDULEID, 
PORTALID) t 
LEFT JOIN 
(SELECT DISTINCT action_id, 
module_iD, 
portal_id, 
opcontent_id 
FROM etl_rule s,(SELECT * 
FROM TABLE ( 
SELECT f_etl_proc ( 
check_value01 
|| ',' 
|| check_value02) 
FROM etl_condition 
WHERE condition_id = 
3)) b 
WHERE rule_valid = 1 
AND rule_type = '点击 ' AND TO_CHAR (action_id) = b.column_value
 
) s 
ON ( t.actionid = s.action_id 
AND t.opcontentid = s.opcontent_id 
AND t.MOUDULEID = s.module_iD 
AND t.PORTALID = s.portal_id) 
WHERE action_id IS NULL

分享到：