Oralce-清除数据的两种思路
【摘要】
文章目录
数据量很大数据量较少配置Job ，定时执行定期进行表分析
数据量很大
思路： 关联的表数据量都很大，获取一次数据时间较长，可以考虑将数据一次性捞出来放到临时表中，只执行一次耗...
数据量很大
思路： 关联的表数据量都很大，获取一次数据时间较长，可以考虑将数据一次性捞出来放到临时表中，只执行一次耗时取数据的查询，放到临时表中，然后通过游标获取临时表中的数据，去对应的表中删除。 同时考虑到业务高峰期，job执行尽量避开业务高峰期。
Warning: 第一次清理数据量比较大的时候，可以通过创建临时表的方式，待第一次清理完成后，后续过期数据数据量小的情况下，可以采用【数据量较少】这种方法加上JOB以及表分析来开展。
create or replace procedure Proc_Clean_Inventory_Data
authid current_user is
v_card_number voucher_card.card_number%type;
v_goods_type_id gm_goods_inst.goods_type_id%type;
v_goods_stat gm_goods_inst.goods_state%type;
v_inventory_clean_record_num number(3);
v_temp_inventory_index_num number(3);
v_createRecordTableSql varchar2(4000);
v_createTempTableIndexSql varchar2(4000);
v_dropTableSql varchar2(4000);
v_getDataSql varchar2(4000);
--- create table temp_inventory_4_delete(card_number varchar2(60));
-- cusrosr card_number collection
cursor cur_card_numbers is
select card_number from TEMP_INVENTORY_4_DELETE;
begin
-- set value
v_goods_type_id :='V';
v_goods_stat :='C';
-- create temp table
v_getDataSql := 'create table TEMP_INVENTORY_4_DELETE as select card_number
from voucher_card vc
where vc.card_number in
(select goods_sn
from gm_goods_inst
where GOODS_TYPE_ID = '||chr(39)||v_goods_type_id||chr(39)||'
and GOODS_STATE = '||chr(39)||v_goods_stat||chr(39)||'
and state_date < sysdate - 50)' ;
v_dropTableSql := 'drop table TEMP_INVENTORY_4_DELETE';
v_createTempTableIndexSql := 'create index IDX_CARD_NUMBER on TEMP_INVENTORY_4_DELETE (CARD_NUMBER)';
v_createRecordTableSql :='create table inventory_clear_record(card_number VARCHAR2(60),create_time DATE)';
-- check exists or not
select count(1) into v_inventory_clean_record_num from user_tables ut where ut.TABLE_NAME = 'INVENTORY_CLEAR_RECORD';
-- create inventory_clear_record for once
if v_inventory_clean_record_num = 0 then
execute immediate v_createRecordTableSql ;
end if ;
-- drop TEMP_INVENTORY_4_DELETE then recreate
execute immediate v_dropTableSql ;
execute immediate v_getDataSql ;
-- check exists or not
select count(1) into v_temp_inventory_index_num from user_indexes ui where ui.index_name = 'IDX_CARD_NUMBER';
-- create index for TEMP_INVENTORY_4_DELETE
if v_temp_inventory_index_num = 0 then
execute immediate v_createTempTableIndexSql ;
end if ;
/******Clean expired Data in voucher_card And gm_goods_inst******/
--open cur_card_numbers,execute the cursor define sql
open cur_card_numbers;
--begin to loop
loop
-- fetch one record into v_vc_id
fetch cur_card_numbers into v_card_number;
exit when cur_card_numbers%notfound;
--dbms_output.put_line('v_card_number:' || v_card_number);
--a.backup one record to voucher_card_old
insert into voucher_card_old select * from voucher_card vc where vc.card_number = v_card_number;
--b.delete one record from voucher_card
delete from voucher_card vc where vc.card_number = v_card_number;
--a.backup one record to gm_goods_inst_old
insert into gm_goods_inst_old select * from gm_goods_inst ggi where ggi.goods_sn = v_card_number;
--b.delete one record from gm_goods_inst
delete from gm_goods_inst ggi where ggi.goods_sn = v_card_number;
--d.insert into inventory_clear_record for tracking deleted data
execute immediate 'insert into inventory_clear_record(card_number,create_time) values(:a,:b)' using v_card_number ,sysdate ;
--dbms_output.put_line('rowcount:' || cur_card_numbers%rowcount);
-- 1000 once ,commit
if mod(cur_card_numbers%rowcount,1000) = 0 then
commit;
--dbms_output.put_line('batch commit:' || cur_card_numbers%rowcount);
end if ;
end loop;
--close cursor
if cur_card_numbers%isopen then
close cur_card_numbers;
end if;
commit;
--excption
exception
when others then
rollback;
end Proc_Clean_Inventory_Data;
数据量较少
如果数据量较少，可以直接将数据加载到游标中，分多次执行。
create or replace procedure Proc_Clean_Inventory_Data
authid current_user is
v_card_number voucher_card.card_number%type;
v_inventory_clean_record_num number(3);
v_createRecordTableSql varchar2(1000);
-- cusrosr card_number collection
cursor cur_card_numbers is
select card_number
from voucher_card vc
where vc.card_number in
(select goods_sn
from gm_goods_inst
where GOODS_TYPE_ID = 'V'
and GOODS_STATE = 'C'
and state_date < sysdate - 50)
--and rownum <= 100000
;
begin
v_createRecordTableSql :='create table inventory_clear_record(card_number VARCHAR2(60),create_time DATE)';
-- check exists or not
select count(1) into v_inventory_clean_record_num from user_tables ut where ut.TABLE_NAME = 'INVENTORY_CLEAR_RECORD';
-- create inventory_clear_record for once
if v_inventory_clean_record_num = 0 then
execute immediate v_createRecordTableSql ;
end if ;
/******Clean expired Data in voucher_card And gm_goods_inst******/
--open cur_card_numbers,execute the cursor define sql
open cur_card_numbers;
--begin to loop
loop
-- fetch one record into v_vc_id
fetch cur_card_numbers into v_card_number;
exit when cur_card_numbers%notfound;
--dbms_output.put_line('v_card_number:' || v_card_number);
--a.backup one record to voucher_card_old
insert into voucher_card_old select * from voucher_card vc where vc.card_number = v_card_number;
--b.delete one record from voucher_card
delete from voucher_card vc where vc.card_number = v_card_number;
--a.backup one record to gm_goods_inst_old
insert into gm_goods_inst_old select * from gm_goods_inst ggi where ggi.goods_sn = v_card_number;
--b.delete one record from gm_goods_inst
delete from gm_goods_inst ggi where ggi.goods_sn = v_card_number;
--d.insert into inventory_clear_record for tracking deleted data
execute immediate 'insert into inventory_clear_record(card_number,create_time) values(:a,:b)' using v_card_number ,sysdate ;
--dbms_output.put_line('rowcount:' || cur_card_numbers%rowcount);
-- 1000 once ,commit
if mod(cur_card_numbers%rowcount,1000) = 0 then
commit;
--dbms_output.put_line('batch commit:' || cur_card_numbers%rowcount);
end if ;
end loop;
--close cursor
if cur_card_numbers%isopen then
close cur_card_numbers;
end if;
commit;
--excption
exception
when others then
rollback;
end Proc_Clean_Inventory_Data;
配置Job ，定时执行
begin
sys.dbms_job.submit(job => :job,
what => 'proc_clean_inventory_data;',
next_date => to_date('09-05-2018 02:00:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'TRUNC(sysdate+1)+2/24');
commit;
end;
/
定期进行表分析
我们这两个表是分区表，表分析SQL如下，主要是 GRANULARITY
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'CRM',
TABNAME => 'GM_GOODS_INST',
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
METHOD_OPT => 'for all columns size repeat',
DEGREE => 4,
GRANULARITY => 'ALL',
CASCADE => TRUE,
no_invalidate => false);
END;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'CRM',
TABNAME => 'VOUCHER_CARD',
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
METHOD_OPT => 'for all columns size repeat',
DEGREE => 4,
GRANULARITY => 'ALL',
CASCADE => TRUE,
no_invalidate => false);
END;
文章来源: artisan.blog.csdn.net，作者：小小工匠，版权归原作者所有，如需转载，请联系作者。
原文链接：artisan.blog.csdn.net/article/details/80237996
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
作者其他文章
评论（0）