Please help combining with clause scans into one

Question

I have this query below that runs in acceptable time, however was questioned if it could be improved by eliminating aggregation scans (combining 'prv' and 'nxt' "with clause" scans into one), however am unable to find how to actually do it....

any help would be appreciated

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);

Since only difference in where clause is ( a.publ_date > m.publ_date vs a.publ_date < m.publ_date ) and dense_rank order I tried removing the aforementioned condition from where clause combine two queries into one using something like:

max(case when A.PUBL_DATE > M.PUBL_DATE then A.magazine_ID end ) KEEP (DENSE_RANK FIRST ORDER BY A.PUBL_DATE) AS NEXT_magazine_ID,

however that did not produce expected results. What would be other ways to possibly make that work? Thank you.

Answer 1

You can put the join conditions from NXT and PRV together in a separate common table expression and then use a CASE expression to separate them later. Although this change actually creates more common table expressions, it stops the code from repeating itself, which is the real reason behind common table expressions.

...
,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,
...