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

sbornstein2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sbornstein2Author Commented:
thanks perfect
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.