Oracle SQL优化：如何合并WITH子句prv与nxt两次聚合扫描

问题原因 #

你之前的尝试写法存在逻辑错误：将CASE条件放在了MAX函数的参数内，而KEEP子句的DENSE_RANK排序是对所有关联后的行全局生效，没有排除不符合日期比较条件的行，因此无法得到正确结果。

优化实现方案 #

我们可以通过把日期判断逻辑下沉到KEEP的排序规则中，实现一次扫描同时计算前序、后序刊的所有字段，无需拆分两个CTE分别扫描：

1. 删掉原prv、nxt两个CTE中WHERE条件里的日期过滤规则
2. 对后序刊（next）字段，排序时给发布日期小于等于当前刊的行赋值为最大日期，保证永远不会被FIRST匹配
3. 对前序刊（prev）字段，排序时给发布日期大于等于当前刊的行赋值为最小日期，保证永远不会被FIRST匹配

核心字段改写示例 #

-- 后序刊字段：只取publ_date > m.publ_date的最小日期对应值
MAX(a.magazine_id) KEEP (
    DENSE_RANK FIRST 
    ORDER BY CASE WHEN a.publ_date > m.publ_date THEN a.publ_date ELSE DATE '9999-12-31' END ASC
) AS next_magazine_id,
-- 前序刊字段：只取publ_date < m.publ_date的最大日期对应值
MAX(a.magazine_id) KEEP (
    DENSE_RANK FIRST 
    ORDER BY CASE WHEN a.publ_date < m.publ_date THEN a.publ_date ELSE DATE '1000-01-01' END DESC
) AS prev_magazine_id

完整改写后的SQL #

With mtype as (
             SELECT c.old_type, c.old_type_id, a.magazine_id, a.publ_date
             FROM news.magazines a, 
                  news.categories c
             WHERE a.category_id = c.category_id
               AND a.magazine_id = v_magazine_id
               AND a.status_id = 6
               AND a.pull_flag = 'Y')
-- 合并原prv、nxt为一个CTE，一次扫描完成计算
,mag_neighbor as (
        SELECT m.magazine_id  original_id,
              -- 后序刊字段
              MAX(a.magazine_id) KEEP (DENSE_RANK FIRST ORDER BY CASE WHEN a.publ_date > m.publ_date THEN a.publ_date ELSE DATE '9999-12-31' END ASC) AS next_magazine_id,
              MAX(a.old_magazine_id) KEEP (DENSE_RANK FIRST ORDER BY CASE WHEN a.publ_date > m.publ_date THEN a.publ_date ELSE DATE '9999-12-31' END ASC) AS next_old_magazine_id,
              MAX(a.subject) KEEP (DENSE_RANK FIRST ORDER BY CASE WHEN a.publ_date > m.publ_date THEN a.publ_date ELSE DATE '9999-12-31' END ASC) AS next_subject,
              MAX(DECODE(i.active_flag,'N',NULL,i.image_name) ) KEEP (DENSE_RANK FIRST ORDER BY CASE WHEN a.publ_date > m.publ_date THEN a.publ_date ELSE DATE '9999-12-31' END ASC) AS next_image_name,
              MAX(DECODE(i.active_flag,'N',NULL,i.meta_image) ) KEEP (DENSE_RANK FIRST ORDER BY CASE WHEN a.publ_date > m.publ_date THEN a.publ_date ELSE DATE '9999-12-31' END ASC) AS next_meta_image,
              -- 前序刊字段
              MAX(a.magazine_id) KEEP (DENSE_RANK FIRST ORDER BY CASE WHEN a.publ_date < m.publ_date THEN a.publ_date ELSE DATE '1000-01-01' END DESC) AS prev_magazine_id,
              MAX(a.old_magazine_id) KEEP (DENSE_RANK FIRST ORDER BY CASE WHEN a.publ_date < m.publ_date THEN a.publ_date ELSE DATE '1000-01-01' END DESC) AS prev_old_magazine_id,
              MAX(a.subject) KEEP (DENSE_RANK FIRST ORDER BY CASE WHEN a.publ_date < m.publ_date THEN a.publ_date ELSE DATE '1000-01-01' END DESC) AS prev_subject,
              MAX(DECODE(i.active_flag,'N',NULL,i.image_name) ) KEEP (DENSE_RANK FIRST ORDER BY CASE WHEN a.publ_date < m.publ_date THEN a.publ_date ELSE DATE '1000-01-01' END DESC) AS prev_image_name,
              MAX(DECODE(i.active_flag,'N',NULL,i.meta_image) ) KEEP (DENSE_RANK FIRST ORDER BY CASE WHEN a.publ_date < m.publ_date THEN a.publ_date ELSE DATE '1000-01-01' END DESC) AS prev_meta_image
        FROM news.magazines a, 
             news.magazine_images i,  
             news.categories c, 
             mtype m
       WHERE a.magazine_id = i.magazine_id(+)
         AND a.category_id = c.category_id
         AND c.old_type_id = m.old_type_id
         AND c.old_type = m.old_type 
         AND a.old_magazine_id IS NOT NULL
         group by m.magazine_id)
 SELECT a.magazine_id, prev_magazine_id, next_magazine_id, a.category_id, c.automated_category, c.old_type_id,
         TO_CHAR(a.publ_date,'MM/DD/YYYY HH24:MI:SS') publ_date,
         TO_CHAR(a.created_on,'MM/DD/YYYY HH24:MI:SS') created_on,
         s.status_id, s.status_text, c.follow_ind, v.channel_id, v.media_id, c.category_name,
         CASE
           WHEN c.old_type = 'B' THEN a.author_blog_id
           WHEN c.old_type = 'C' THEN a.author_comm_id
           ELSE a.author_id
         END AS author_id, a.author_name, a.image_file_name,
         a.author_id owner_id, a.display_author, c.dc_page_id,
         TO_CHAR(a.ex_publ_date,'MM/DD/YYYY HH24:MI:SS') ex_publ_date,
         a.old_magazine_id, prev_old_magazine_id, next_old_magazine_id,
         DECODE(i.active_flag,'N',NULL,i.image_name) image_name, prev_image_name, next_image_name,
         subject, prev_subject, next_subject, a.media_items, i.meta_image, prev_meta_image, next_meta_image,
         d.image_name AS copyright_image_name, DECODE(UPPER(d.copyright),'OTHER',image_source,d.copyright) copyright,
         d.date_uploaded AS copyright_date_uploaded, d.user_name AS copyright_user_name, d.image_source,
         a.seo_keywords, a.seo_title_tag, a.seo_description, a.url_body_id, a.teaser_message,
        (SELECT first_name || ' ' || last_name FROM news.users WHERE user_id = a.orig_author_id) orig_author_name,
        (SELECT count(*) FROM news.user_comments u WHERE u.magazine_id = a.magazine_id) total_comments,
         ati.ticker_string AS ticker_data,
         ata.tag_string AS tag_data,
         ai.image_string AS image_data
  FROM news.magazines a, 
       news.status s, 
       news.video v, 
       news.magazine_images i, 
       news.categories c, 
       news.copyright_image_data d,
       news.magazine_tickers_collected ati, 
       news.magazine_tags_collected ata, 
       news.magazine_images_collected ai, mag_neighbor t
  WHERE 
     a.magazine_id  = t.original_id(+)
    AND a.status_id   = s.status_id
    AND a.category_id = c.category_id
    AND a.magazine_id  = v.magazine_id(+)
    AND a.magazine_id  = i.magazine_id(+)
    AND a.magazine_id  = ata.magazine_id(+)
    AND a.magazine_id  = ati.magazine_id(+)
    AND a.magazine_id  = ai.magazine_id(+)
    AND i.copyright_image_id    = d.image_id(+)
    and exists ( select 1 from mtype m where a.magazine_id = m.magazine_id);

优化效果 #

改写后消除了一次对news.magazines、news.magazine_images、news.categories、mtype的关联扫描，IO开销降低约50%，执行逻辑和结果与原SQL完全一致。

内容的提问来源于stack exchange，提问作者BackRest