How to regroup nested choices?

Question

I have such a request with total amounts (this is a simplified 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 incredibly slow. Since both nested selections look the same, I wanted to regroup them. Sort of

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

but I can’t 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

Answer 1

The aggregate amounts (totals) calculated by self-join are usually slow because for each record all the previous records repeated must be read again.

It is better to use the SUM analytic function instead of:

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