SQL Optimization: query table with different where clauses

Question

I have the following SQL query:

SELECT (
    SELECT SUM(c1)
    FROM t2
    WHERE t1.c1 = t2.c1
    AND t2.c3 = 'NEW'
    AND t2.c4 = TRUNC(SYSDATE)
    AND t2.c5 = 'N'
) SUM,
(
    SELECT sum(c2)
    FROM t2
    WHERE t1.c1 = t2.c1
    AND t2.c3 = 'OLD'
    AND t2.c4 = TRUNC(SYSDATE)
) SUM2
FROM t1
WHERE t1.c2 IN ('DUMMY', 'DUMMY2')

So, the t2 table get queried multiple times but with different WHERE clauses each time. This t2 table is very large and so it takes some time to get the results. It would be good to only query this table once with all the different WHERE clauses and SUM results..

Is this possible anyway? I got a suggestion to use a WITH AS in the SQL, but with no success for my execution time

Answer 1

You could have several sum calls over case expression in t2, and then join that to t1:

SELECT sum1, sum2
FROM   t1
JOIN   (SELECT c1, 
               SUM(CASE WHEN c3 = 'NEW' AND 
                             c4 = TRUNC(SYSDATE) AND 
                             c5 = 'N' THEN c1 
                        ELSE NULL END) AS sum1,
               SUM(CASE WHEN c3 = 'OLD' AND 
                             c4 = TRUNC(SYSDATE) THEN c2 
                        ELSE NULL END) AS sum2
        FROM   t2) t2 ON t1.c1 = t2.c1
WHERE  t1.c2 IN ('DUMMY', 'DUMMY2')

EDIT: The common conditions in the case expressions (i.e., c4 = TRUNC(SYSDATE)) can be extracted to a where clause, which should provide some performance gain:

SELECT sum1, sum2
FROM   t1
JOIN   (SELECT c1, 
               SUM(CASE WHEN c3 = 'NEW' AND c5 = 'N' THEN c1 
                        ELSE NULL END) AS sum1,
               SUM(CASE WHEN c3 = 'OLD' THEN c2 
                        ELSE NULL END) AS sum2
        FROM   t2
        WHERE  c4 = TRUNC(SYSDATE)) t2 ON t1.c1 = t2.c1
WHERE  t1.c2 IN ('DUMMY', 'DUMMY2')

Answer 2

This query gave me same results as your original SQL with sample data:

with 
  a as (select c1 from t1 where c2 in ('DUMMY', 'DUMMY2')),
  b as (
    select c1,
        sum (case when c3 = 'NEW' and c5 = 'N' then c1 end) sum1,
        sum (case when c3 = 'OLD' then c2 end) sum2
      from t2 
      where c4 = trunc(sysdate) and exists (select 1 from a where c1 = t2.c1)
      group by c1
    )
select * from a left join b using (c1)

^SQLFiddle

... and should be faster, as I tried to limit it only to the necessary steps. If column c1 is unique in table t1 than perhaps query may be simplified, but I didn't assume uniqueness here.

BTW - are you sure that for sum1 you are summing column c1, not c2?

Answer 3

You can try this:

SELECT SUM1.val, SUM2.val
FROM (SELECT * FROM t1 WHERE t1.c2 IN ('DUMMY', 'DUMMY2')) as t1
INNER JOIN (
    SELECT SUM(c1) as val
    FROM t2
    WHERE t2.c3 = 'NEW'
        AND t2.c4 = TRUNC(SYSDATE)
        AND t2.c5 = 'N'
) SUM1
    ON t1.c1 = SUM1.c1
INNER JOIN (
    SELECT SUM(c2) as val
    FROM t2
    WHERE t2.c3 = 'OLD'
        AND t2.c4 = TRUNC(SYSDATE)
) SUM2
    ON t1.c1 = SUM2.c1