oracle - 请帮助将子句扫描合二为一

Question

我在下面有这个在可接受的时间内运行的查询，但是有人质疑它是否可以通过消除聚合扫描来改进（将“prv”和“nxt”“with 子句”扫描合并为一个），但是我无法找到实际操作它....

任何帮助，将不胜感激

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')
,nxt as (
        SELECT m.magazine_id  original_id,
              MAX(a.magazine_id) KEEP (DENSE_RANK FIRST ORDER BY a.publ_date) AS next_magazine_id,
              MAX(a.old_magazine_id) KEEP (DENSE_RANK FIRST ORDER BY a.publ_date) AS next_old_magazine_id,
              MAX(a.subject) KEEP (DENSE_RANK FIRST ORDER BY a.publ_date) AS next_subject,
              MAX(DECODE(i.active_flag,'N',NULL,i.image_name) ) KEEP (DENSE_RANK FIRST ORDER BY a.publ_date) AS next_image_name,
              MAX(DECODE(i.active_flag,'N',NULL,i.meta_image) ) KEEP (DENSE_RANK FIRST ORDER BY a.publ_date) AS next_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
         AND a.publ_date > m.publ_date
         group by m.magazine_id)
,prv as
         (
         SELECT m.magazine_id  original_id,
             MAX(a.magazine_id) KEEP (DENSE_RANK FIRST ORDER BY a.publ_date DESC) AS prev_magazine_id,
             MAX(a.old_magazine_id) KEEP (DENSE_RANK FIRST ORDER BY a.publ_date DESC) AS prev_old_magazine_id,
             MAX(a.subject) KEEP (DENSE_RANK FIRST ORDER BY a.publ_date DESC) AS prev_subject,
             MAX(DECODE(i.active_flag,'N',NULL,i.image_name) ) KEEP (DENSE_RANK FIRST ORDER BY a.publ_date DESC) AS prev_image_name,
             MAX(DECODE(i.active_flag,'N',NULL,i.meta_image) ) KEEP (DENSE_RANK FIRST ORDER BY a.publ_date 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
           AND a.publ_date < m.publ_date
         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, nxt x, prv y
  WHERE 
     a.magazine_id  = x.original_id(+)
    AND a.magazine_id  = y.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);

由于 where 子句的唯一区别是（ a.publ_date > m.publ_date vs a.publ_date < m.publ_date ）和 dense_rank 顺序，我尝试从 where 子句中删除上述条件，使用以下方法将两个查询合并为一个：

最大值（A.PUBL_DATE > M.PUBL_DATE 然后 A.magazine_ID end 的情况）保持（A.PUBL_DATE 的 DENSE_RANK FIRST ORDER BY A.PUBL_DATE）作为 NEXT_magazine_ID，

然而，这并没有产生预期的结果。还有什么其他方法可以使这项工作发挥作用？谢谢你。

标签： oracledense-rankoracle-analytics

Answer 1

您可以将来自 NXT 和 PRV 的连接条件放在一个单独的公用表表达式中，然后使用 CASE 表达式将它们分开。尽管这种更改实际上创建了更多通用表表达式，但它阻止了代码重复自身，这也是通用表表达式背后的真正原因。

...
,next_or_prev as
(
      SELECT m.magazine_id  original_id, a.publ_date,
             CASE WHEN a.publ_date > m.publ_date THEN 'NEXT' ELSE 'PREV' END is_next_or_prev
        FROM ...
       WHERE ...
             --Include both less than and greater than predicates in one.
             AND a.publ_date <> m.publ_date
             ...
)
,nxt as
(
      SELECT m.magazine_id  original_id,
             MAX(a.magazine_id) KEEP (DENSE_RANK FIRST ORDER BY a.publ_date) AS next_magazine_id,
             ... NEXT_ columns here ...
        FROM next_or_prev
       WHERE is_next_or_prev = 'NEXT'
    GROUP BY ...
)
,prv as
(
      SELECT m.magazine_id  original_id,
             MAX(a.magazine_id) KEEP (DENSE_RANK FIRST ORDER BY a.publ_date DESC) AS prev_magazine_id,
             ... PREV_ columns here ...
        FROM next_or_prev
       WHERE is_next_or_prev = 'PREV'
    GROUP BY ...
)
...
(SELECT * FROM nxt) x,
(SELECT * FROM prv) y,
...