oracle-databaseplsqloracle11gcursortable-functions

How can I do this faster than cursor?


I have 2 tables:

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;

Solution

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