请帮助将子句扫描合并为一个

Question

我下面有一个查询，它在可接受的时间内运行，但有人问我是否可以通过消除聚合扫描(将"prv"one_answers"nxt"与子句扫描合并为一个(来改进它，但我无法找到如何实际执行…

如有任何帮助，将不胜感激

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与a.publ_date<m.publ_date(和dense_rank顺序我尝试从where子句中删除前面提到的条件，该子句使用类似于：

max(A.PUBL_DATE>M.PUBL_DATE然后A.magazine_ID结束时的情况(保持(A.PUBL_DATE拒绝ANK FIRST ORDER(为NEXT_GAZINE_ID，

然而，这并没有产生预期的结果。还有什么其他方法可以让它发挥作用？非常感谢。

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