数据量很大
思路： 关联的表数据量都很大，获取一次数据时间较长，可以考虑将数据一次性捞出来放到临时表中，只执行一次耗时取数据的查询，放到临时表中，然后通过游标获取临时表中的数据，去对应的表中删除。 同时考虑到业务高峰期，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;
