Mike, So it looks like you are going thru the table a lot of times. Why not try this - note that there are no loops involved. It is all one query. And are you *sure* you need to join the table to the v_ldgr_fact_denorm_mapv2rpt view/table? You are not using any columns from that table in the insert statement. I would also look at parallelizing any tables involved. Again, Good Luck! Insert Into t_ldgr_fact_rpt (prod_id_cd, proces_dt_txt, acct_subset_cd, dept_id_cd, biz_unt_nm, scenr_id, mtd_amt, ytd_amt, qtd_amt, mix_annl_mtd_amt, mix_annl_ytd_amt, mix_annl_qtd_amt, actl_annl_mtd_amt, actl_annl_ytd_amt, actl_annl_qtd_amt ) Select /*+ index (a I_LDGR_FACT_DENORM ) */ a.prod_id_cd, a.proces_dt_txt, T_SUBSET.acct_subset_cd a.dept_id_cd, a.biz_unt_nm, a.Scenr_id, Sum(a.mtd_amt), Sum(a.ytd_amt), Sum(a.qtd_amt), Sum(a.mix_annl_mtd_amt), Sum(a.mix_annl_ytd_amt), Sum(a.mix_annl_qtd_amt), Sum(a.actl_annl_mtd_amt), Sum(a.actl_annl_ytd_amt), Sum(a.actl_annl_qtd_amt) From v_ldgr_fact_denorm_mapv2rpt a, t_rpt_acct_subset_temp b, ( Select Distinct acct_subset_cd From T_RPT_ACCT_SUBSET) T_SUBSET Where a.acct_subset_cd = b.acct_nbr And b.acct_subset_cd = T_SUBSET.acct_subset_cd Group By a.prod_id_cd, a.proces_dt_txt, a.dept_id_cd, a.biz_unt_nm, a.scenr_id; Tom Mercadante Oracle Certified Professional -----Original Message----- From: mkline1@xxxxxxxxxxx [mailto:mkline1@xxxxxxxxxxx] Sent: Friday, March 19, 2004 1:36 PM To: oracle-l@xxxxxxxxxxxxx Subject: RE: Slow Inserts I got James to give me more of the code that causes the problem. He said this was the part that takes the 7.5 hours. He said the rest runs reasonably good. I hope this gives us enough to work on. Michael: The SQL listed below is the part that takes so long. /* -------------------------------------------------------------------------*/ /* Create the T_LDGR_FACT_RPT table */ /* -------------------------------------------------------------------------*/ Declare v_acct_subset_cd t_rpt_acct_subset.acct_subset_cd%TYPE; /* -------------------------------------------------------------------------*/ /* Cursor to select all current month rows from the t_ldgr_fact table */ /* -------------------------------------------------------------------------*/ Cursor c_rpt_acct_subset IS Select Distinct acct_subset_cd From T_RPT_ACCT_SUBSET; Begin /* -------------------------------------------------------------------------*/ /* Open cursor */ /* -------------------------------------------------------------------------*/ Open c_rpt_acct_subset; /* -------------------------------------------------------------------------*/ /* Start of loop */ /* -------------------------------------------------------------------------*/ LOOP Fetch c_rpt_acct_subset INTO v_acct_subset_cd; EXIT WHEN c_rpt_acct_subset%NOTFOUND; Insert Into t_ldgr_fact_rpt (prod_id_cd, proces_dt_txt, acct_subset_cd, dept_id_cd, biz_unt_nm, scenr_id, mtd_amt, ytd_amt, qtd_amt, mix_annl_mtd_amt, mix_annl_ytd_amt, mix_annl_qtd_amt, actl_annl_mtd_amt, actl_annl_ytd_amt, actl_annl_qtd_amt ) Select /*+ index (a I_LDGR_FACT_DENORM ) */ a.prod_id_cd, a.proces_dt_txt, v_acct_subset_cd, a.dept_id_cd, a.biz_unt_nm, a.Scenr_id, Sum(a.mtd_amt), Sum(a.ytd_amt), Sum(a.qtd_amt), Sum(a.mix_annl_mtd_amt), Sum(a.mix_annl_ytd_amt), Sum(a.mix_annl_qtd_amt), Sum(a.actl_annl_mtd_amt), Sum(a.actl_annl_ytd_amt), Sum(a.actl_annl_qtd_amt) From v_ldgr_fact_denorm_mapv2rpt a, t_rpt_acct_subset_temp b Where a.acct_subset_cd = b.acct_nbr And b.acct_subset_cd = v_acct_subset_cd Group By a.prod_id_cd, a.proces_dt_txt, a.dept_id_cd, a.biz_unt_nm, a.scenr_id; /* -------------------------------------------------------------------------*/ /* End of the loop */ /* -------------------------------------------------------------------------*/ End Loop; Commit; /* -------------------------------------------------------------------------*/ /* Close the cursor */ /* -------------------------------------------------------------------------*/ Close c_rpt_acct_subset; END; -- Michael Kline, Principle Consultant Business To Business Solutions 13308 Thornridge Ct Midlothian, VA 23112 804-744-1545 > The information transmitted is intended only for the person or entity to > which it is addressed and may contain confidential and/or privileged > material. If the reader of this message is not the intended recipient, > you are hereby notified that your access is unauthorized, and any review, > dissemination, distribution or copying of this message including any > attachments is strictly prohibited. If you are not the intended > recipient, please contact the sender and delete the material from any > computer. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------