How can I do this faster than cursor?

Question

I have 2 tables:

• first is : dept
• second is : payment

I want to compare these tables and make one result table .

Tables include :

table debt:

customerid   order  amount
1              1      30
1              2      50
1              3      70

table payment:

customerid   recorddate  amount
1            20080101      10
1            20080102      20
1            20080103      180

And I want result table is :

customerid   recorddate  amount    order
1            20080101      10       1
1            20080102      20       1
1            20080103      50       2
1            20080103      70       2
1            20080103      60       -

I make this result with 2 cursor. And I have 1 million record and It takes too long. How can I make this faster?

thanks in advance

EDIT

I did it with this code:

DECLARE 
V_RECORDDATE DATE; 
V_CUSTOMERID VARCHAR2(500CHAR); 
V_PAYMENT NUMBER;  
CURSOR TAH_HES IS
SELECT  /*+ PARALLEL(16) */ * FROM
payment_table
WHERE customerid='1' 
ORDER BY 3,1;
BEGIN
EXECUTE IMMEDIATE 'alter session force parallel query parallel 16';
EXECUTE IMMEDIATE 'alter session force parallel dml parallel 16';

OPEN TAH_HES;
  LOOP
      FETCH TAH_HES INTO V_RECORDDATE, V_CUSTOMERID,V_PAYMENT;
      EXIT WHEN TAH_HES%NOTFOUND;



FOR CUR_X IN ( 
SELECT  /*+ PARALLEL(16) */  
COMPENENT,
AMOUNT
 FROM
DEBT_TABLE
WHERE 1=1
AND CUSTOMERID=V_CUSTOMERID  
ORDER BY "ORDER"
 )
LOOP
IF(CUR_X.AMOUNT<=V_PAYMENT) 
THEN


 INSERT INTO
RESULT_TABLE
SELECT  /*+ PARALLEL(16) */ 
V_CUSTOMERID,V_RECORDDATE,CUR_X.COMPENENT,CUR_X.AMOUNT
 FROM
DUAL;

COMMIT;

DELETE FROM
DEBT_TABLE 
WHERE CUSTOMERID=V_CUSTOMERID 
AND COMPENENT=CUR_X.COMPENENT
AND AMOUNT=CUR_X.AMOUNT;

COMMIT;



UPDATE
PAYMENT_TABLE
SET PAYMENT=PAYMENT-CUR_X.AMOUNT
WHERE CUSTOMERID=V_CUSTOMERID 
AND HISLEMTARIH=V_RECORDDATE;

COMMIT;


ELSE

 INSERT INTO
RESULT_TABLE
SELECT  /*+ PARALLEL(16) */ 
V_CUSTOMERID,V_RECORDDATE,CUR_X.COMPENENT,V_PAYMENT
 FROM
DUAL;

COMMIT;


UPDATE
DEBT_TABLE 
SET AMOUNT=AMOUNT-V_PAYMENT
WHERE CUSTOMERID=V_CUSTOMERID
AND COMPENENT=CUR_X.COMPENENT ;

COMMIT;



DELETE FROM
PAYMENT_TABLE
WHERE CUSTOMERID=V_CUSTOMERID 
AND PAYMENT=V_PAYMENT
AND RECORDDATE=V_RECORDDATE;

COMMIT;


EXIT;

END IF;
END LOOP;





END LOOP;

END;

INSERT INTO
RESULT_TABLE
SELECT  /*+ PARALLEL(16) */ 
CUSTOMERID,
RECORDDATE,
'-',
PAYMENT
 FROM
PAYMENT_TABLE;

COMMIT;

Answer 1

If payments are used to clear off the debt then your result is a bit unexpected.

More logically

payments 10 and 20 will clear off order 1: 30 = 10 + 20
payment 180 will clear off order 2: 50 (180 - 50 = 130 remaining)
payment 180 will clear off order 3: 70 (130 - 70 = 60 remaining)

Or in pure SQL

with debt(customerid, ord, amount) as
(
select 1, 1, 30 from dual
union all select 1, 2, 50 from dual
union all select 1, 3, 70 from dual
),
payment(customerid, recorddate, amount) as
(
select 1, 20080101, 10 from dual
union all select 1, 20080102, 20 from dual
union all select 1, 20080103, 180 from dual
),
allocation as
(
select *
from
(select d.customerid, d.ord, d.amount, p.recorddate, p.amount as pay_amount
from debt d
join payment p on d.customerid = p.customerid)
model ignore nav
partition by (customerid)
dimension by (recorddate, ord)
measures(amount, pay_amount, 0 allocated)
  rules
  (
   allocated[any, any] order by ord, recorddate =
   least(pay_amount[cv(recorddate), cv(ord)] - 
         sum(allocated)[cv(recorddate), ord <= cv(ord)]
        ,amount[cv(recorddate), cv(ord)] - 
         sum(allocated)[recorddate <= cv(recorddate), cv(ord)])
  )
)
select a.*, pay_amount - 
            sum(allocated) over (partition by recorddate order by ord) remaining
from allocation a
where allocated > 0
order by ord, recorddate;

CUSTOMERID RECORDDATE        ORD     AMOUNT PAY_AMOUNT  ALLOCATED  REMAINING
---------- ---------- ---------- ---------- ---------- ---------- ----------
         1   20080101          1         30         10         10          0
         1   20080102          1         30         20         20          0
         1   20080103          2         50        180         50        130
         1   20080103          3         70        180         70         60

PL/SQL solution would be more optimal for this task (but not your implementation).