Purging [message #314103] |
Tue, 15 April 2008 15:39  |
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 #314119 is a reply to message #314103] |
Tue, 15 April 2008 16:11   |
flyboy
Messages: 1903 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   |
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   |
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  |
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;
/
|
|
|