Oracle Query Optimization Help - Multi Pass

Question

I need to query the orders table to get a count of all orders for yesterdays transactions, grouped by the ship date. Then I need to have an additional column to give the total orders for the ship date for all transactions. When I added this second column, time of processing grew exponentially (which was expected) to 109s. Is there any way I could improve this SQL? I just want to know if I'm missing anything fundamental here.

SELECT t.shipping_date
       , t.net_orders
       , count(*) as total_orders
FROM (
    SELECT   s.store_num
             , s.store_cd
             , to_char(o.shipping_date, 'MM/DD/YYYY') as shipping_date
             , COUNT (*) as net_orders
    FROM order o left 
           join store s 
                on ( s.store_num = o.store_num )
    WHERE TRUNC (o.order_date) = TRUNC (SYSDATE - 1)
    AND s.store_cd = 'ZZZ'
    AND o.status in ('A', 'B')
    GROUP BY s.store_num
             , s.store_cd
             , to_char(shipping_date, 'MM/DD/YYYY')
) t
LEFT JOIN order o ON 
          ( TRUNC (o.shipping_date) = to_date(t.shipping_date, 'MM/DD/YYYY') 
            and o.store_num = t.store_num )
WHERE o.status in ('A', 'B')
GROUP BY t.shipping_date, t.net_orders;

I have indexes on all of these columns in addition to the following expressions: TRUNC(order_date) and TRUNC(shipping_date).

Answer 1

I re-wrote your query as:

   SELECT t.shipping_date, 
          t.net_orders, 
          COUNT(*) as total_orders 
     FROM (SELECT s.store_num, 
                  s.store_cd, 
                  o.status,
                  TRUNC(o.shipping_date) AS shipping_date, 
                  COUNT (*) as net_orders
             FROM STORE s
             JOIN ORDER o ON o.store_num = s.store_num
                         AND o.status IN ('A', 'B')
            WHERE s.store_cd = 'ZZZ'
              AND TRUNC(order_date) = TRUNC (SYSDATE - 1)
  GROUP BY s.store_num, s.store_cd, TRUNC(shipping_date)) t
LEFT JOIN ORDER o ON TRUNC(o.shipping_date) = t.shipping_date
                 AND o.store_num = t.store_num
                 AND o.status = t.status
 GROUP BY t.shipping_date, t.net_orders;

Some minor re-arrangement, but I did get rid of the TO_CHAR(shipping_date) which is later converted back to a DATE. TRUNC(shipping_date) is the same, and simplifies the operation.

Joining on criteria using functions won't use an index - you'll need to create a function based index matching the JOIN criteria.

Answer 2

If you're just looking for output like:

shipping_date      net_orders     total_orders
01-AUG-2004        14             37
02-AUG-2004        17             29
03-AUG-2004        19             43

how about just:

SELECT *
  FROM (
    SELECT TRUNC(o.shipping_date) as shipping_date
         , COUNT(CASE WHEN TRUNC(o.order_date) = TRUNC(SYSDATE - 1) 
                      THEN 1 
                      ELSE NULL 
                  END) as net_orders -- count does not count NULL values.
         , COUNT(*) as total_orders
      FROM order o 
           LEFT JOIN 
           store s 
              on s.store_num = o.store_num
     WHERE s.store_cd = 'ZZZ'
       AND o.status in ('A', 'B')
     GROUP BY TRUNC(o.shipping_date)
   ) 
WHERE net_orders > 0 -- only shipping dates that had at least one order yesterday

It'll avoid the extra join back to the orders table and since you're going to have to touch all of the orders for the store anyways, while you're getting the total count, you can also do what I'd call a conditional count at the same time.