文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >优化联合所有查询oracle 11G？

问优化联合所有查询oracle 11G？
EN

Stack Overflow用户
提问于 2018-07-30 10:41:22
回答 1查看 47关注 0票数 0

有人能看一下优化这个查询吗？相同的复杂查询与UNION一起多次加入，在每个集合中都有不同的“最后更新日期”条件。我可以通过添加一个条件来缩短下面的查询，而不重复相同的查询，这将给出相同的结果，但是查询运行的时间要长10倍。

代码语言：javascript
复制
(
AND dist_all.last_update_date > TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS')
    OR line_locations_all.last_update_date > TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS')
       OR line_all.last_update_date > TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS')
          OR head_all.last_update_date > TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS')
             OR releases_all.last_update_date > TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS') )

查询如下：

代码语言：javascript
复制
SELECT
    *
FROM
   table 1.. table 8
 WHERE

      dist_all.last_update_date > TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS')
 /*all join conditions here */

 UNION ALL
 SELECT
     *
 FROM
    table 1.. table 8
 WHERE
      dist_all.last_update_date <= TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS')
     AND line_locations_all.last_update_date > TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS')
     /*all join conditions here */
 UNION ALL
 SELECT
     *
 FROM
     table 1.. table 8
 WHERE

     dist_all.last_update_date <= TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS')
     AND line_locations_all.last_update_date <= TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS')
     AND line_all.last_update_date > TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS')
 /*all join conditions here */
 UNION ALL
 SELECT
     *
 FROM
    table 1.. table 8
 WHERE
      dist_all.last_update_date <= TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS')
     AND line_locations_all.last_update_date <= TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS')
     AND line_all.last_update_date <= TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS')
     AND head_all.last_update_date > TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS')
  /*all join conditions here */    
 SELECT
     *
 FROM
    table 1.. table 8
WHERE
    dist_all.last_update_date <= TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS')
    AND line_locations_all.last_update_date <= TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS')
        AND line_all.last_update_date <= TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS')
            AND head_all.last_update_date <= TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS')
                AND releases_all.last_update_date > TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS')
 /*all join conditions here */
sql
oracle
EN

回答 1

Stack Overflow用户

发布于 2018-07-30 11:14:39

尝尝这个

代码语言：javascript
复制
WITH DTL AS
(
    SELECT da.last_update_date as da_last_update_date,
            lla.last_update_date as lla_last_update_date,
            line_all.last_update_date as la_last_update_date,
            head_all.last_update_date as ha_last_update_date,
            releases_all.last_update_date as ra_last_update_date,
            .......
    FROM head_all ha
            INNER JOIN dist_all da on (ha.header_id = da.header_id)
            INNER JOIN line_all la on (da.line_id = la.line_id )
            INNER JOIN line_locations_all lla on (da.line_location_id = lla.line_location_id )
            LEFT JOIN req_distributions_all rda on (rda.distribution_id = da.req_distribution_id)
            LEFT JOIN requisition_lines_all rla on (rda.requisition_line_id = rla.requisition_line_id) 
            LEFT JOIN requisition_headers_all rha on (rla.requisition_header_id = rha.requisition_header_id)
            LET JOIN releases_all ra on (lla.release_id = ra.release_id) 
            LEFT JOIN head_all contract on (la.contract_num = contract.segment1) 
            LEFT JOIN head_all blanket_agreement on (la.from_header_id = blanket_agreement.header_id)
            LEFT JOIN line_types_b ltb on (lla.line_id = ltb.line_type_id)
    WHERE line_locations_all.shipment_type NOT IN ( 'PRICE BREAK', 'PLANNED' ) 
    AND head_all.type_lookup_code IN ( 'BLANKET', 'STANDARD', 'PLANNED' ) 
)
Select * from dtl
Where da_last_update_date > TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS') 
UNION ALL 
SELECT * from dtl
Where da_last_update_date <= TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS') 
AND lla_last_update_date > TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS') 
UNION ALL 
SELECT * From dtl
Where da_last_update_date <= TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS') 
AND lla_last_update_date <= TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS') 
AND la_last_update_date > TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS') 
UNION ALL 
SELECT * From dtl
Where da_last_update_date <= TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS') 
AND lla_last_update_date <= TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS') 
AND la_last_update_date <= TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS') 
AND ha_last_update_date > TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS') 
UNION ALL 
SELECT * From dtl
WHERE da_last_update_date <= TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS') 
AND lla_last_update_date <= TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS') 
AND la_last_update_date <= TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS') 
AND ha_last_update_date <= TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS') 
AND ra_last_update_date > TO_DATE('07/01/2018 00:00:01','MM/DD/YYYY HH24:MI:SS')

注意:在with子句中，所有其他必需列都应该用正确的别名列出(这就是为什么我把.)

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/51592153

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档