[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Simplifying union query (advanced)

Posted on 2013-10-31
1
Medium Priority
?
355 Views
Last Modified: 2013-11-11
This query is having performance issues..

was wondering if anyone knows a way to simplify it

WITH DESCR AS (SELECT 
              RMCUSP.CUSREGNUM AS CHAIN, 
              RMCODP.CODLNGDES AS CHAIN_DESCRIPTION
          FROM 
            RMCODP, 
            RMCUSP  
          WHERE CUSREGNUM = CODKEYNUM AND CODCODTYP='RN'
          GROUP BY CUSREGNUM, CODLNGDES),
          
          
 BRANDS AS (SELECT  DISTINCT
                    RMITMP.ITMITMBRD AS BRAND_NO, 
                    RMCODP.CODLNGDES AS BRAND
                    FROM
                    RMITMP,
                    RMCODP
         WHERE ITMITMBRD = CODKEYNUM AND CODCODTYP = 'IB'), 
          
     ORDERS AS (
  
     SELECT 
                RMCUSP.CUSREGNUM AS CHAIN, 
                RMCODP.CODLNGDES AS CHAIN_DESCRIPTION, BRAND,
                round((Sum(RMORDP.ORDORDQTY/RMDCIP.DCICASFAC)),2) AS ORDERS,
                RMORHP.ORHDELDTE AS ORD_DATE
            FROM 
                RMCODP,
                RMCUSP,
                RMITMP, 
                RMORDP,
                RMORHP,
                RMDCIP,
                BRANDS
            WHERE CODKEYNUM = CUSREGNUM 
              AND ORDITMNUM = ITMITMNUM 
              AND CUSCUSNUM = ORHCUSNUM 
              AND CUSCUSCHN = ORHCUSCHN 
              AND ORHORDNUM = ORDORDNUM 
              AND DCIDSTCTR = ORHDSTCTR 
              AND DCIITMNUM = ORDITMNUM 
              AND BRAND_NO = ITMITMBRD
              AND CODCODTYP='RN' 
              AND CUSREGNUM <> 185 
              AND ORDITMNUM NOT IN(98,99, 994) 
              AND ORHDELDTE BETWEEN 20131013 AND 20131019
              AND DCICASFAC<>0  
            GROUP BY RMCUSP.CUSREGNUM, RMCODP.CODLNGDES, RMCODP.CODCODTYP,RMORHP.ORHDELDTE,BRAND    
UNION ALL 
           SELECT 
              CUSREGNUM AS CHAIN, 
              CODLNGDES,BRAND,
              round((Sum(RMORDP.ORDORDQTY/RMDCIP.DCICASFAC)),2) AS ORDERS , RMORHP.ORHDELDTE AS ORD_DATE 
           FROM RMCODP, RMCUSP, RMITMP, RMORDP, RMORHP,RMDCIP ,BRANDS 
          WHERE CODKEYNUM = CUSREGNUM 
            AND ORDITMNUM = ITMITMNUM 
            AND CUSCUSNUM = ORHCUSNUM 
            AND CUSCUSCHN = ORHCUSCHN 
            AND ORHORDNUM = ORDORDNUM 
            AND DCIDSTCTR = ORHDSTCTR 
            AND DCIITMNUM = ORDITMNUM 
            AND BRAND_NO = ITMITMBRD
            AND CODCODTYP = 'RN' 
            AND ITMPRDGRP <> 480 
            AND CUSREGNUM = 185 
            AND ORHDELDTE BETWEEN 20131013 AND 20131019 
            AND DCICASFAC<>0 
            AND ORDITMNUM NOT IN(98,99, 994) 
            GROUP BY CUSREGNUM, CODLNGDES, CODCODTYP, RMORHP.ORHDELDTE, BRAND),
  
SALES AS (     SELECT CUSREGNUM AS CHAIN, 
            CODLNGDES, BRAND, RMSLDP.SLDDELDTE AS SALE_DATE ,
            round((Sum(RMSLDP.SLDTRNQTY/RMDCIP.DCICASFAC)),2) AS SALES  
       FROM 
            RMCODP, 
            RMCUSP,
            RMITMP, 
            RMSLDP, 
            RMDCIP,
            BRANDS
      WHERE SLDITMNUM = DCIITMNUM 
        AND CODKEYNUM = CUSREGNUM 
        AND SLDITMNUM = ITMITMNUM 
        AND CUSCUSNUM = SLDCUSNUM 
        AND CUSCUSCHN = SLDCUSCHN
       AND CUSDFTDCN = DCIDSTCTR
        AND BRAND_NO = ITMITMBRD
        AND CODCODTYP='RN' 
        AND (CUSREGNUM <> 185) 
        AND SLDITMNUM NOT IN(98,99, 994) 
        AND SLDDELDTE BETWEEN 20131013 AND 20131019  
        AND DCICASFAC <> 0  
   GROUP BY CUSREGNUM, CODLNGDES, CODCODTYP , RMSLDP.SLDDELDTE ,BRAND  
  
  UNION ALL                         
  
     SELECT CUSREGNUM AS CHAIN, 
            CODLNGDES, BRAND, RMSLDP.SLDDELDTE AS SALE_DATE,
            round((Sum(RMSLDP.SLDTRNQTY/RMDCIP.DCICASFAC)),2) AS SALES 
       FROM RMCODP, RMCUSP, RMITMP, RMSLDP , RMDCIP , BRANDS 
      WHERE SLDITMNUM = DCIITMNUM 
        AND CODKEYNUM = CUSREGNUM 
        AND SLDITMNUM = ITMITMNUM 
        AND CUSCUSNUM = SLDCUSNUM 
        AND CUSCUSCHN = SLDCUSCHN 
        AND CUSDFTDCN = DCIDSTCTR  
        AND BRAND_NO = ITMITMBRD
        AND CODCODTYP = 'RN' 
        AND ITMPRDGRP <> 480 
        AND CUSREGNUM = 185 
        AND SLDDELDTE BETWEEN 20131013 AND 20131019 
        AND DCICASFAC <> 0    
        AND SLDITMNUM NOT IN(98,99, 994) 
   GROUP BY CUSREGNUM, CODLNGDES, CODCODTYP, RMSLDP.SLDDELDTE, BRAND)
   
   
   
select descr.chain,
       descr.chain_description,
      orders.ord_date,
       sales.sale_date,
      orders.orders,
       sales.sales,
       sales.brand
  from (descr LEFT OUTER join orders on orders.chain = descr.chain) LEFT OUTER join sales on sales.chain = descr.chain
where ord_date = sale_date and orders.brand = sales.brand
 
From: John Terrero [mailto:johnt@dorasnaturals.com] 
Sent: Thursday, October 31, 2013 1:22 PM
To: julios@dorasnaturals.com
Subject: try this
 
 
SELECT 
     chain,
     chain_description, 
     round(sum(orders)) orders,
     round(sum(sales)) sales, 
     round(sum(orders - sales)*-1) as difference 
FROM casediflv0
WHERE ord_date BETWEEN 20131013 AND 20131019
GROUP BY 
chain, chain_description
ORDER BY chain
 
 
 

Open in new window

0
Comment
Question by:FutureDBA-
1 Comment
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 39615691
A side note: it may not improve performance, but the syntax like this:
FROM 
                RMCODP,
                RMCUSP,
                RMITMP, 
                RMORDP,
                RMORHP,
                RMDCIP,
                BRANDS

Open in new window

is now considered not standard. To make it ANSI-compliant you should switch to INNER JOIN. As I said it is a syntax change only and may not improve performance.

To check the performance please run this query with EXPLAIN PLAN. Then check what is taking too much time. You may need to add some indices to tables
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month18 days, 18 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question