• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

BULK COLLECT - Does this syntax look correct?

This compiles but it really drags and my toad comes back not responding.  I am wondering if I need to clear the TABLES I am using to store the two key pair?  
DECLARE

CURSOR TRNCUR IS
SELECT TRM.TRANS_REV_ID,
         CASE (SELECT COUNT(*) FROM DISTRIBUTOR_TRAIL dt WHERE dt.distributor_id = d.distributor_id)
         WHEN 0 THEN ROUND((NVL(MGMT_FEE,0) * .5),2)
         ELSE
         CASE 
              WHEN NVL(DT.TRAIL_FEE_BPS,0) = 0 AND NVL(DT.MGMT_FEE_TRAIL ,0) = 0 
                 AND NVL(DT.DIST_FEE_TRAIL,0) = 0
              THEN 0
                WHEN NVL(DT.TRAIL_FEE_BPS,0) > 0
                THEN ROUND((NVL(TRM.AVG_AUM,0) * ((DT.TRAIL_FEE_BPS/100)/12)),2)
                    WHEN NVL(DT.MGMT_FEE_TRAIL ,0) > 0 AND NVL(DT.DIST_FEE_TRAIL,0) > 0
                    THEN ROUND((NVL(TRM.MGMT_FEE,0) * (DT.MGMT_FEE_TRAIL/100))+(NVL(TRM.DISTRIBUTION_FEE,0) * (DT.DIST_FEE_TRAIL/100)),2)
                       WHEN NVL(DT.MGMT_FEE_TRAIL,0) > 0
                       THEN ROUND((NVL(TRM.MGMT_FEE,0) * (DT.MGMT_FEE_TRAIL/100)),2)
                          WHEN NVL(DT.DIST_FEE_TRAIL,0) > 0
                          THEN ROUND((NVL(TRM.DISTRIBUTION_FEE,0) * (DT.DIST_FEE_TRAIL/100)),2)
         END
         END AS TRAIL_FEE   
         FROM TRANS_REVENUES TRM
         INNER JOIN ALLOCATION ALLOC ON TRM.alloc_key = ALLOC.alloc_key
         INNER JOIN DISTRIBUTOR D ON ALLOC.distributor_id = D.distributor_id AND TRIM(UPPER(D.distributor_name)) <> 'FOF'    
         LEFT JOIN DISTRIBUTOR_TRAIL DT ON D.distributor_id = DT.distributor_id                      
                           AND (
                                (dt.FUND_ID = TRM.FUND_ID AND dt.CLASS_ID = TRM.CLASS_ID) OR
                                (dt.FUND_ID = TRM.FUND_ID AND dt.CLASS_ID = '-1') OR
                                (dt.FUND_ID = '-1' AND dt.CLASS_ID = '-1')
                               ) 
                           AND ((TRM.AVG_AUM BETWEEN NVL(DT.ASSETS_MIN, TRM.AVG_AUM)
                                           AND NVL(DT.ASSETS_MAX, TRM.AVG_AUM)) OR
                                 (GREATEST (NVL (DT.ASSETS_MIN, 0), NVL (DT.ASSETS_MAX, 0)) = 0))
                           AND ((DT.effective_start_date >= TO_DATE(2009 || '-' || 4 || '-01', 'yyyy-mm-dd')
                           AND DT.effective_end_date <= TO_DATE(2009 || '-' || 4 || '-01', 'yyyy-mm-dd'))
                           OR (DT.effective_end_date IS NULL))
         LEFT JOIN EIM_FUND_MASTER EFM ON TRM.fund_id = EFM.fund_id
         WHERE TRM.YEAR = 2009 AND TRM.MONTH = 4 AND TRM.CREATE_SOURCE = 5504
           AND EFM.Product_Family = 1;
           
errors PLS_INTEGER;

dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381); 

type trncur_type is table of TRNCUR%rowtype INDEX BY BINARY_INTEGER;
l_table trncur_type;

TYPE t_rev_id  IS TABLE OF TRANS_REVENUES.TRANS_REV_ID%TYPE INDEX BY BINARY_INTEGER;
TYPE t_trail_fee IS TABLE OF TRANS_REVENUES.TRAIL_FEE%TYPE INDEX BY BINARY_INTEGER;
v_rev_id     t_rev_id;
v_trail_fee t_trail_fee;

BEGIN
OPEN TRNCUR;
LOOP
 FETCH TRNCUR BULK COLLECT INTO l_table LIMIT 10000;

 FOR i IN 1..l_table.count LOOP
      v_rev_id(i) := l_table(i).TRANS_REV_ID;
      v_trail_fee(i) := l_table(i).TRAIL_FEE;
 END LOOP;

 FORALL i IN l_table.FIRST..l_table.LAST SAVE EXCEPTIONS
 UPDATE TRANS_REVENUES SET TRAIL_FEE = v_trail_fee(i)
        WHERE TRANS_REV_ID = v_rev_id(i);
 EXIT WHEN TRNCUR%NOTFOUND;
   
END LOOP;
CLOSE TRNCUR;
dbms_output.put_line('Rows updated: ' || SQL%ROWCOUNT);
COMMIT;

EXCEPTION
  WHEN dml_errors THEN
    errors := SQL%BULK_EXCEPTIONS.COUNT;
    raise_application_error
          (-24381,'Number of UPDATE statements that
            failed: ' || errors
          );

    FOR i IN 1 .. errors
    LOOP
      raise_application_error(-24381,'Error #' || i || ' at '|| 'iteration
      #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
      raise_application_error(-24381,'Error message is ' ||
      SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
    END LOOP;
  WHEN OTHERS THEN
    RAISE; 

END;
/

Open in new window

0
sbornstein2
Asked:
sbornstein2
  • 4
  • 2
1 Solution
 
flow01Commented:
the fetch clears them
(you can check by dbms_output.put_line('t:' || l_table.count)

some suggestions
use SQL%BULK_ROWCOUNT after the forall to check for the number of updates

is there enough free memory oracle can use to store the pl/sql-table
for debugging the trouble area
display time before and after the open
make the limit 100
exit loop after 1 pass and and display start en end-time : of the loop
are the times reasonable ?


0
 
sbornstein2Author Commented:
Ya I am running it right now with a limit of 10000 and its taking already 12 minutes and still going.  I cannot for the life of me get the performance I need. :)
0
 
sbornstein2Author Commented:
Can you help at all with some debug lines in what I have attached here like seeing how long for example 100 rows takes and then exiting and showing time?  I think thats a good idea.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
sbornstein2Author Commented:
I changed the raise_application to a dbms output line as well just FYI.
0
 
flow01Commented:
declare loop_nr pls_integer := 0;
begin
dbms_output.put_line(to_char('sysdate,'dd/mm/yyyy hh24:mi:ss'  ||  ' before open cursor');
open
dbms_output.put_line(to_char('sysdate,'dd/mm/yyyy hh24:mi:ss'  ||  ' after open cursor');
loop
  loop_nr := loop_nr + 1;
  dbms_output.put_line(to_char('sysdate,'dd/mm/yyyy hh24:mi:ss'  ||  ' start of loop ' || loop_nr');
   fetch ..
  dbms_output.put_line(to_char('sysdate,'dd/mm/yyyy hh24:mi:ss'  ||  ' after fetch  of loop ' || loop_nr');
  ..
 dbms_output.put_line(to_char('sysdate,'dd/mm/yyyy hh24:mi:ss'  ||  ' before update of loop ' || loop_nr');
 
 dbms_output.put_line(to_char('sysdate,'dd/mm/yyyy hh24:mi:ss'  ||  ' after update of loop ' || loop_nr' || ' updated:' ||  SQL%BULK_ROWCOUNT);

IF loop_nr  >= 1  THEN  -- for debugging stop after loop 1
   EXIT;
END IF;
END LOOP;
0
 
sbornstein2Author Commented:
thanks perfect
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now