数据量很大

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