HASH JOIN 跟NESTED LOOP 快一个列子

最新推荐文章于 2024-07-10 18:39:57 发布

最新推荐文章于 2024-07-10 18:39:57 发布 · 105 阅读

本文对比了NESTED LOOP与HASH JOIN两种连接方式在处理大量数据时的性能差异，发现HASH JOIN在相同条件下运行速度远超NESTED LOOP。文章通过具体案例展示了如何通过选择合适的连接方法来提高SQL查询效率。

在报表库的每个小时数据量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