前言:
一般說來, 刪資料有delete table, truncate兩種, 如果要刪部分資料,只能用delete table的方式了。
為何要寫另一個暫存索引表格test.usersid_pk 來刪資料?
一般說來, 刪資料有delete table, truncate兩種, 如果要刪部分資料,只能用delete table的方式了。
為何要寫另一個暫存索引表格test.usersid_pk 來刪資料?
因為線上的real.users 表格直接刪5筆檔案仍會hang住...已經很確定瞬間沒有任何table lock...
#針對test.users 的pk (ID) 欄位 , 建一個暫存的index 表格
create table test.usersid_pk tablespace users_bck storage (initial 4M next 4M ) as (select id from test.users where DATE < '20130301')
alter table test.usersid_pk add (seq number);
#建立一個sequence for test.usersid_pk(seq) 欄位
CREATE SEQUENCE test.id_seq MINVALUE 1 START WITH 1 INCREMENT BY 1;
#針對括欄後的test.usersid_pk 表格, 每一筆資料都按照序號發號碼牌
declare
v_counter number:=0;
cursor cur is
select seq from test.usersid_pk for update;
BEGIN
for c_id in cur loop
UPDATE test.usersid_pk
SET seq = (test.id_seq.nextval );
IF MOD( v_counter, 1000) = 0 THEN -- Commits once per 1000 records
commit;
END IF;
v_counter:=v_counter + 1;
END LOOP ;
commit;
END;
/
#使用cursor 於loop內真正的刪除資料於 real.users 表格
#select count(*) test.usersid_pk = 345643筆
sqlplus "/ as sysdba" <<EOF
spool del_usr_tab.log
set timing on
set time on
set echo on
set serverout on
DECLARE
v_myid number:=0;
BEGIN
for v_number in 1..345643 loop
select myid into v_myid from test.usersid_pk where seq = v_number;
delete from real.users where id= v_myid;
IF MOD( v_number, 25 ) = 0 THEN -- Commits once per 25 records
commit;
END IF;
end loop;
END;
/
commit;
EOF
