Home » SQL & PL/SQL » SQL & PL/SQL » Purging (Oracle 10g)
Purging [message #314103] Tue, 15 April 2008 15:39 Go to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
hi,

I am writting a procedure which will purge all the fct tables and store the data in history tables.

i have two methods in mind .
1) Creating the temp table and use it as input.
2)Using cursors

Please suggest which way is better to do.


Thanks
Re: Purging [message #314114 is a reply to message #314103] Tue, 15 April 2008 15:55 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Please suggest which way is better to do.
vanilla SQL from "fct" (fact?) table(s) into history tables.
Re: Purging [message #314118 is a reply to message #314114] Tue, 15 April 2008 16:11 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
anacedent,

I didn't understand what is vanilla?

Re: Purging [message #314119 is a reply to message #314103] Tue, 15 April 2008 16:11 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
With this vague requirement, I would
3) rename the source table and then create it empty
4) do partition exchange with pre-created empty table/partition

I would not go into INSERT AS SELECT followed by DELETE the source in any of its variant, when data is really huge.
But without details, it is really hard to guess what you are trying to implement.
Re: Purging [message #314124 is a reply to message #314119] Tue, 15 April 2008 16:18 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
flyboy,

I am trying to write a procedure which will take date as a parameter.according to given date it will fetch all the data from the various fact table and insert into the various history tables. this procedure will work after every quarter of the year.
Re: Purging [message #314127 is a reply to message #314124] Tue, 15 April 2008 16:57 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
So, you want to put data into a table, and then, every quarter, copy that data to a history table and purge the original table of all data?

Personally, I would create a trigger that immediately copies the data from the "fact" tables to the "history" tables. Then, all I would have to do is truncate the "fact" tables each quarter.

But, without further details, it's hard to actually propose a solution that would work for you.

R
Re: Purging [message #314130 is a reply to message #314103] Tue, 15 April 2008 18:02 Go to previous message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
I tried the procedure like this.. Still it is not complete
May need some modification based on suggestions.
Records in these tables are not more than 50 thousands.
create or replace purge_script(MIS_DATE date)
as
count_sub_exp number;
count_sub_exp_h number;
count_sec_sub number;
count_sec_sub_h number;
count_sec_exp number;
count_sec_exp_h number;
count_risk_summ number;
count_risk_summ_h number;
count_capit number;
count_capit_h number;
count_sec_rep number;
count_sec_rep_h number;
count_memo number;
count_memo_h number;
count_market number;
count_market_h number;
count_asset number;
count_asset_h number;
count_aggr number;
count_aggr_h number;
count_rep_line number;
count_rep_line_h number;

begin

Insert into fct_sub_exposures_his 
select * from fct_sub_exposures where FIC_MIS_DATE=MIS_DATE;
select count(rowid) into count_sub_exp from fct_sub_exposures where FIC_MIS_DATE=MIS_DATE;
select count(rowid) into count_sub_exp_h from fct_sub_exposures where FIC_MIS_DATE=MIS_DATE;
if (count_sub_exp=count_sub_exp_h) then
delete from fct_sub_exposures where FIC_MIS_DATE=MIS_DATE;
commit;
else
raise some_error;
end if;
----------------------------------------------------------------------------------------------
Insert into fct_sec_sub_exp_his 
select * from fct_sec_sub_exp where FIC_MIS_DATE=MIS_DATE;
select count(rowid) into count_sec_sub from fct_sec_sub_exp where FIC_MIS_DATE=MIS_DATE;
select count(rowid) into count_sec_sub_h from fct_sec_sub_exp_his where FIC_MIS_DATE=MIS_DATE;
if (count_sub_exp=count_sub_exp_h) then
delete from fct_sec_sub_exp where FIC_MIS_DATE=MIS_DATE;
commit;
else
raise some_error;
end if;
----------------------------------------------------------------------------------------------
Insert into fct_sec_sub_exposures_his
select * from fct_sub_exposures where FIC_MIS_DATE=MIS_DATE;
select count(rowid) into count_sec_exp  from fct_sec_sub_exposures where FIC_MIS_DATE=MIS_DATE;
select count(rowid) into count_sec_exp_h from fct_sec_sub_exposures_his where FIC_MIS_DATE=MIS_DATE;
if (count_sec_exp=count_sec_exp_h) then
delete from fct_sec_sub_exp where FIC_MIS_DATE=MIS_DATE;
commit;
else
raise some_error;
end if;
----------------------------------------------------------------------------------------------
Insert into fct_risk_summary_his 
select * from fct_risk_summary where FIC_MIS_DATE=MIS_DATE;
select count(rowid) into count_risk_summ  from fct_risk_summary where FIC_MIS_DATE=MIS_DATE;
select count(rowid) into count_risk_summ_h from fct_risk_summary_his where FIC_MIS_DATE=MIS_DATE;
if (count_risk_summ=count_risk_summ_h) then
delete from fct_risk_summary where FIC_MIS_DATE=MIS_DATE;
commit;
else
raise some_error;
end if;
-------------------------------------------------------------------------------------------------
Insert into fct_capital_rep_line_his 
select * from fct_capital_rep_line where FIC_MIS_DATE=MIS_DATE;
select count(rowid) into count_capit  from fct_capital_rep_line where FIC_MIS_DATE=MIS_DATE;
select count(rowid) into count_capit_h from fct_capital_rep_line_his where FIC_MIS_DATE=MIS_DATE;
if (count_capit=count_capit_h) then
delete from fct_capital_rep_line where FIC_MIS_DATE=MIS_DATE;
commit;
else
raise some_error;
end if;
-------------------------------------------------------------------------------------------------
Insert into fct_sec_rep_line_summary_his 
select * from fct_sec_rep_line_summary where FIC_MIS_DATE=MIS_DATE;
select count(rowid) into count_sec_rep  from fct_sec_rep_line_summary where FIC_MIS_DATE=MIS_DATE;
select count(rowid) into count_sec_rep_h from fct_sec_rep_line_summary_his where FIC_MIS_DATE=MIS_DATE;
if (count_sec_rep=count_sec_rep_h) then
delete from fct_capital_rep_line where FIC_MIS_DATE=MIS_DATE;
commit;
else
raise some_error;
end if;
--------------------------------------------------------------------------------------------------
Insert into fct_memo_type_his 
select * from fct_memo_type where FIC_MIS_DATE=MIS_DATE;
select count(rowid) into count_memo  from fct_memo_type where FIC_MIS_DATE=MIS_DATE;
select count(rowid) into count_memo_h from fct_memo_type_his where FIC_MIS_DATE=MIS_DATE;
if (count_memo=count_memo_h) then
delete from fct_memo_type where FIC_MIS_DATE=MIS_DATE;
commit;
else
raise some_error;
end if;
----------------------------------------------------------------------------------------------------
Insert into fct_market_risk_rep_line_his 
select * from fct_sec_sub_exp where FIC_MIS_DATE=MIS_DATE;
select count(rowid) into count_market  from fct_market_risk_rep_line where FIC_MIS_DATE=MIS_DATE;
select count(rowid) into count_market_h from fct_market_risk_rep_line_his where FIC_MIS_DATE=MIS_DATE;
if (count_market=count_market_h) then
delete from fct_market_risk_rep_line where FIC_MIS_DATE=MIS_DATE;
commit;
else
raise some_error;
end if;
------------------------------------------------------------------------------------------------------
Insert into fct_asset_type_his 
select * from fct_asset_type where FIC_MIS_DATE=MIS_DATE;
select count(rowid) into count_asset  from fct_asset_type where FIC_MIS_DATE=MIS_DATE;
select count(rowid) into count_asset_h from fct_asset_type_his where FIC_MIS_DATE=MIS_DATE;
if (count_asset=count_asset_h) then
delete from fct_asset_type where FIC_MIS_DATE=MIS_DATE;
commit;
else
raise some_error;
end if;
------------------------------------------------------------------------------------------------------
Insert into fct_aggr_summary_his 
select * from fct_aggr_summary where FIC_MIS_DATE=MIS_DATE;
select count(rowid) into count_aggr  from fct_aggr_summary where FIC_MIS_DATE=MIS_DATE;
select count(rowid) into count_aggr_h from fct_aggr_summary_his where FIC_MIS_DATE=MIS_DATE;
if (count_asset=count_asset_h) then
delete from from fct_aggr_summary where FIC_MIS_DATE=MIS_DATE;
commit;
else
raise some_error;
end if;
-----------------------------------------------------------------------------------------------------
Insert into fct_sec_report_line_summary_his 
select * from fct_sec_report_line_summary where FIC_MIS_DATE=MIS_DATE;
select count(rowid) into count_rep_line  from fct_sec_report_line_summary where FIC_MIS_DATE=MIS_DATE;
select count(rowid) into count_rep_line_h from fct_sec_report_line_summary_his where FIC_MIS_DATE=MIS_DATE;
if (count_rep_line=count_rep_line_h) then
commit;
delete from fct_sec_report_line_summary where FIC_MIS_DATE=MIS_DATE;
else
raise some_error;
end if;
------------------------------------------------------------------------------------------------------

EXCEPTION
WHEN some_error
DBMS_OUTPUT.PUT_LINE('Error');
END;
/

Previous Topic: bulk collect problem
Next Topic: hierarchy
Goto Forum:
  


Current Time: Sun Dec 04 14:33:48 CST 2016

Total time taken to generate the page: 0.14082 seconds