| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Slow Inserts
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_at_comcast.net [mailto:mkline1_at_comcast.net]
Sent: Friday, March 19, 2004 1:36 PM
To: oracle-l_at_freelists.org
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.
/*
-------------------------------------------------------------------------*/
-------------------------------------------------------------------------*/
Declare
v_acct_subset_cd t_rpt_acct_subset.acct_subset_cd%TYPE;
/*
-------------------------------------------------------------------------*/
-------------------------------------------------------------------------*/
Cursor c_rpt_acct_subset IS
Select Distinct acct_subset_cd
From T_RPT_ACCT_SUBSET;
Begin
/*
-------------------------------------------------------------------------*/
-------------------------------------------------------------------------*/
Open c_rpt_acct_subset;
/*
-------------------------------------------------------------------------*/
-------------------------------------------------------------------------*/
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 Loop;
Commit;
/*
-------------------------------------------------------------------------*/
-------------------------------------------------------------------------*/
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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Mar 19 2004 - 12:43:31 CST
 |
 |