华企号 元宇宙 Oracle 删除单体大表的方法

Oracle 删除单体大表的方法

本文档介绍了大表清理的几种方法。

注意:

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.

作者: 华企网通王鹏程序员

我是程序员王鹏,热爱互联网软件开发和设计,专注于大数据、数据分析、数据库、php、java、python、scala、k8s、docker等知识总结。 我的座右铭:"业精于勤荒于嬉,行成于思毁于随"
上一篇
下一篇

发表回复

联系我们

联系我们

028-84868647

在线咨询: QQ交谈

邮箱: tech@68v8.com

工作时间:周一至周五,9:00-17:30,节假日休息

关注微信
微信扫一扫关注我们

微信扫一扫关注我们

关注微博
返回顶部