本文档介绍了大表清理的几种方法。
注意：
1 避免终止delete 大事务，因为会产生回滚，耗费更长的时间。
2 尝试将大型的delete/truncate 拆分为多个事务。
创建测试表
conn joe/joe;
create table ob1 as select * from dba_objects;
insert into ob1 select * from ob1;
insert into ob1 select * from ob1;
...
create table ob2 as select * from ob1;
create table ob3 as select * from ob1;
create table ob4 as select * from ob1;
SQL> select count(*) from ob1;
COUNT(*)
----------
5536064
SQL> select count(*) from ob2;
COUNT(*)
----------
5536064
SQL> select count(*) from ob3;
COUNT(*)
----------
5536064
SQL> select count(*) from ob4;
COUNT(*)
----------
5536064
SQL> select count(*) from ob1 where object_type='SYNONYM';
COUNT(*)
----------
2181440
SQL> select count(*) from ob2 where object_type='SYNONYM';
COUNT(*)
----------
2181440
SQL> select count(*) from ob3 where object_type='SYNONYM';
COUNT(*)
----------
2181440
SQL> select count(*) from ob4 where object_type='SYNONYM';
COUNT(*)
----------
2181440
SQL> select bytes/1024/1024m from dba_segments where segment_name='OB1';
M
----------
624
SQL> select bytes/1024/1024m from dba_segments where segment_name='OB2';
M
----------
624
SQL> select bytes/1024/1024m from dba_segments where segment_name='OB3';
M
----------
624
SQL> select bytes/1024/1024m from dba_segments where segment_name='OB4';
M
----------
624
重启数据库，并关闭归档（为了避免删除的时候产生大量归档消耗空间），然后进行测试。
传统方式删除
SQL> set timing on
SQL> delete ob1 where object_type='SYNONYM';
2181440 rows deleted.
Elapsed: 00:03:53.23
SQL> commit;
Commit complete.
Elapsed: 00:00:00.27
SQL> alter system checkpoint;
System altered.
Elapsed: 00:00:37.95
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:10.84
使用DBMS_PARALLEL_EXECUTE 包进行删除
SET SERVEROUTPUT ON
BEGIN
DBMS_PARALLEL_EXECUTE.DROP_TASK ('test_task');
EXCEPTION WHEN OTHERS THEN
NULL;
END;
/
DECLARE
l_task VARCHAR2(30) := 'test_task';
l_sql_stmt VARCHAR2(32767);
l_try NUMBER;
l_status NUMBER;
BEGIN
-- Create the TASK
DBMS_PARALLEL_EXECUTE.CREATE_TASK (task_name => l_task);
-- Chunk the table by the ROWID
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID
(
TASK_NAME => l_task,
TABLE_OWNER => 'JOE',
TABLE_NAME => 'OB2',
BY_ROW => TRUE,
CHUNK_SIZE => 2500
);
-- DML to be execute in parallel
l_sql_stmt := 'delete OB2 where object_type = ''SYNONYM'' and rowid BETWEEN :start_id AND :end_id';
-- Run the task
DBMS_PARALLEL_EXECUTE.RUN_TASK
(
TASK_NAME => l_task,
SQL_STMT => l_sql_stmt,
LANGUAGE_FLAG => DBMS_SQL.NATIVE,
PARALLEL_LEVEL => 1
);
-- If there is error, RESUME it for at most 2 times.
l_try := 0;
l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(l_task);
WHILE(l_try < 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED)
LOOP
l_try := l_try + 1;
DBMS_PARALLEL_EXECUTE.RESUME_TASK(l_task);
l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(l_task);
END LOOP;
-- Done with processing; drop the task
DBMS_PARALLEL_EXECUTE.DROP_TASK(l_task);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error in the code :' || SQLERRM);
END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:03:00.69
SQL> alter system checkpoint;
System altered.
SQL> alter system flush buffer_cache;
System altered.
涉及相关试图：
SELECT * FROM DBA_PARALLEL_EXECUTE_TASKS;
SELECT * FROM DBA_PARALLEL_EXECUTE_CHUNKS;
SELECT count(1) FROM DBA_PARALLEL_EXECUTE_CHUNKS;
select status, count(*) from user_parallel_execute_chunks group by status;
select D.owner,D.job_name,D.JOB_STYLE,D.JOB_TYPE,D.JOB_ACTION from dba_scheduler_jobs d where d.owner='JOE';
select sid, serial#, status, PROGRAM, SQL_ID, event from v$session where action like 'TASK$%';
使用rowid 进行删除
declare
cursor cur_rowid is
select dbms_rowid.rowid_create(1,
b.data_object_id,
a.relative_fno,
a.block_id,
0) begin_rowid,
dbms_rowid.rowid_create(1,
b.data_object_id,
a.relative_fno,
a.block_id + blocks - 1,
999) end_rowid
from dba_extents a, dba_objects b
where a.segment_name = b.object_name
and a.owner = b.owner
and b.object_name = 'OB3'
and b.owner = 'JOE'
order by a.relative_fno, a.block_id;
r_sql varchar2(4000);
begin
FOR cur in cur_rowid LOOP
r_sql := 'delete OB3 where object_type =' || '''' || 'SYNONYM' || '''' ||
' and rowid between :1 and :2';
EXECUTE IMMEDIATE r_sql
using cur.begin_rowid, cur.end_rowid;
COMMIT;
END LOOP;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:02:52.10
SQL> alter system checkpoint;
System altered.
SQL> alter system flush buffer_cache;
System altered.
使用rowid 分片的方式进行删除
vi rowid_chunk.sql
set verify off
undefine rowid_ranges
undefine segment_name
undefine owner
set head off
set pages 0
set trimspool on
select 'where rowid between ''' ||sys.dbms_rowid.rowid_create(1, d.oid, c.fid1, c.bid1, 0) ||''' and ''' ||sys.dbms_rowid.rowid_create(1, d.oid, c.fid2, c.bid2, 9999) || '''' ||';'
from (select distinct b.rn,
first_value(a.fid) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) fid1,
last_value(a.fid) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) fid2,
first_value(decode(sign(range2 - range1),
1,
a.bid +
((b.rn - a.range1) * a.chunks1),
a.bid)) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) bid1,
last_value(decode(sign(range2 - range1),
1,
a.bid +
((b.rn - a.range1 + 1) * a.chunks1) - 1,
(a.bid + a.blocks - 1))) over(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) bid2
from (select fid,
bid,
blocks,
chunks1,
trunc((sum2 - blocks + 1 - 0.1) / chunks1) range1,
trunc((sum2 - 0.1) / chunks1) range2
from (select /*+ rule */
relative_fno fid,
block_id bid,
blocks,
sum(blocks) over() sum1,
trunc((sum(blocks) over()) / &&rowid_ranges) chunks1,
sum(blocks) over(order by relative_fno, block_id) sum2
from dba_extents
where segment_name = upper('&&segment_name')
and owner = upper('&&owner'))
where sum1 > &&rowid_ranges) a,
(select rownum - 1 rn
from dual
connect by level <= &&rowid_ranges) b
where b.rn between a.range1 and a.range2) c,
(select max(data_object_id) oid
from dba_objects
where object_name = upper('&&segment_name')
and owner = upper('&&owner')
and data_object_id is not null) d
/
@rowid_chunk.sql
Enter value for rowid_ranges: 1
Enter value for segment_name: ob4
Enter value for owner: joe
SQL> delete OB4 where object_type ='SYNONYM' and rowid between 'AAAWCUAAEAAALaAAAA' and 'AAAWCUAAEAACix/CcP';
2181440 rows deleted.
Elapsed: 00:02:56.64
SQL> commit;
Commit complete.
