Solved

Simplifying union query (advanced)

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

830 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