Solved

Simplifying union query (advanced)

Posted on 2013-10-31
1
307 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 24

Accepted Solution

by:
chaau earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now