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;

Please post the code for your cursor-based approach - from your sample data, it's not 100% clear what you want to accomplish. — Frank Schmitt, Oct 17 '17 at 06:58
what is the connection between debt, payment and result table? Is it like a puzzle? — Kaushik Nayak, Oct 17 '17 at 07:05
Unless your tables are partitioned and really huge I don't see any benefit in forcing `PARALLEL`, it may even slowdown you operation. Using `SELECT /*+ PARALLEL(16) */ ... FROM dual` is definitely useless. — Wernfried Domscheit, Oct 17 '17 at 07:53
Please provide a working example. Columns `COMPENENT` and `HISLEMTARIH` are not part your your table definitions. — Wernfried Domscheit, Oct 17 '17 at 08:18

Dr Y Wit · Accepted Answer · 2017-10-17T10:56:36.027

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

PL/SQL means in this case: 1) load debt into collection or temporary table 2) go through payments using cursor, calculate allocations and maintain remaining debt in the collection — Dr Y Wit, Oct 17 '17 at 11:12
It's problem with implementation not with cursor. You should not be using DML and moreover it does not makes sense to use commit after each statement etc. Demonstrated implementation is extremely suboptimal. Read this carefully http://www.oracle.com/technetwork/database/features/plsql/overview/doing-sql-from-plsql-129775.pdf — Dr Y Wit, Oct 17 '17 at 11:19

How can I do this faster than cursor?

table debt:

table payment:

And I want result table is :

EDIT

1 Answers1