CODEWITHSUNDEEP
sqloracle-database
ThomasThiebaud
ThomasThiebaud

Reputation: 11999

How to regroup nested select?

I have this kind of request with cumulative sums (this is a simplify example)

INSERT INTO DEMO (a,b,c,d,e)
  SELECT 
    DATA.a,
    NVL((SELECT SUM r2.x FROM EXAMPLE r2 WHERE r2.a = r1.a AND r2.i <= r1.i),0),
    DATA.c,
    NVL((SELECT SUM r2.y FROM EXAMPLE r2 WHERE r2.a = r1.a AND r2.i <= r1.i),0),
    DATA.e
  FROM
    DATA
    LEFT OUTER JOIN EXAMPLE r1 ON DATA.a = r1.a

This request works but is incredibly slow. Since both the nested select looks the same, I wanted to regroup them. Something like 

SELECT SUM r2.x, SUM r2.y FROM EXAMPLE r2 WHERE r2.a = r1.a AND r2.i <= r1.i

but I can not successfully achieve this. How can I do it ?

I tried both 

INSERT INTO DEMO (a,c,e,b,d)
  SELECT 
    DATA.a,
    DATA.c,
    DATA.e
    (SELECT SUM r2.x, SUM r2.y FROM EXAMPLE r2 WHERE r2.a = r1.a AND r2.i <= r1.i)
  FROM DATA
  LEFT OUTER JOIN EXAMPLE r1
    ON DATA.a = r1.a

and

INSERT INTO DEMO (a,c,e,b,d)
  SELECT 
    DATA.a,
    DATA.c,
    DATA.e
  FROM DATA
  LEFT OUTER JOIN EXAMPLE r1
    ON DATA.a = r1.a
  UNION
  SELECT
    (SELECT SUM r2.x, SUM r2.y FROM EXAMPLE r2 WHERE r2.a = r1.a AND r2.i <= r1.i)
  FROM DATA
  LEFT OUTER JOIN EXAMPLE r1
    ON DATA.a = r1.a

Upvotes: 4

Views: 67

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

Cumulative sums (running totals) computed with self joins are usually slow, because for every record all matching former records must be read again.

Better use the analytic function SUM instead:

INSERT INTO DEMO (a,b,c,d,e)
  SELECT 
    DATA.a,
    NVL( SUM(x) OVER (PARTITION BY r1.a ORDER BY r1.i
                      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) , 0),
    DATA.c,
    NVL( SUM(y) OVER (PARTITION BY r1.a ORDER BY r1.i
                      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) , 0),
    DATA.e
  FROM DATA
  LEFT OUTER JOIN EXAMPLE r1
    ON DATA.a = r1.a

Upvotes: 3

Related Questions