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')