HASH JOIN 跟NESTED LOOP 快一个列子

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

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

1 ·

CC 4.0 BY-SA版权

文章标签：

#nested #join #action #date #table #module

Oracle 开发专栏收录该内容

128 篇文章

订阅专栏

本文对比了NESTED LOOP与HASH JOIN两种SQL连接方式在处理大量数据时的效率差异，前者耗时3分钟，后者仅需7秒。通过具体的SQL示例说明了在报表库中进行数据查询时不同连接方式的性能表现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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