How to Find SHOUG?

Size: px

Start display at page:

Download "How to Find SHOUG?"

龛姜花
7 years ago
Views:

1 ORACLE 清理历史数据方法介绍 by SHOUG. 林少杰 (kevinlin.ora)

2 How to Find SHOUG?

3 清理历史数据方法介绍 Delete data 本文作者 : 林少杰 (kevinlin.ora@gmail.com ) 摘要 : 无关键词 :cursor;rowid;delete;ctas;expdp/impdp 使用 delete 命令清理数据方法在很多情况下, 只能使用 delete 命令来清理数据, 这时需要关注回滚段使用日志产生量以及清理效率, 尤其在大数据量的情况下, 需要合理选择清理方法 delete 操作对大数据量操作后, 表中出现较多的空闲数据块, 索引中出现较多标记为删除的记录如果有较多对表的全表扫描操作, 建议 shrink 表 ; 如果对索引有全索引扫描或范围索引扫描, 建议重建索引或合并索引 (coalesce) 更多更好的办法 : 利用 rowid 分块实现非分区表的并行 update 与 delete.html 本文仅针对非分区表具体内容 : 一 delete 命令清理数据 delete 操作对大数据量操作后, 表中出现较多的空闲数据块, 索引中出现较多标记为删除的记录如果有较多对表的全表扫描操作, 建议 shrink 表 ; 如果对索引有全索引扫描或范围索引扫描, 建议重建索引或合并索引 (coalesce) 使用 delete 命令进行数据清理的方法一般有三种 : 1. 使用全表扫描进行一次性全部删除, 单次提交命令 : Delete /*+ full(table_name) */ from table_name where condition;

4 Commit; 优点 : 速度最快缺点 : 对回滚段需求较大大数据量时, 容易在中途出现 ora-1555 错误, 导致删除失败, 并且引起大数据量的回滚 2. 使用全表扫描进行多次部分删除, 分批 commit ( 亦可多条不同 where 条件的 delete 语句同时运行, 减小物理读总量 ) 命令 : Delete from table_name where condition; Commit; 优点 : 对回滚段要求相对较小, 一定程度上根据改变条件可以控制回滚空间需求量缺点 : 速度较慢在中途出现 ora-1555 错误时, 会导致较多的回滚操作批次越多, 总处理时间越长 3. 使用全表扫描与 rowid 进行一次性删除, 分批 commit 命令 : DECLARE CURSOR table_name_cur IS SELECT /*+ FULL(a) */ a.rowid from table_name a where time_column<sysdate-xxx ; table_name_rec table_name_cur%rowtype; row_number number; BEGIN row_number :=0; OPEN table_name_cur; LOOP FETCH table_name_cur INTO table_name_rec; IF table_name_cur%notfound THEN EXIT; END IF; delete from table_name WHERE rowid = table_name_rec.rowid; row_number := row_number + 1;

5 if (mod (row_number,1000) =0) then insert into delete_rows values (row_number); end if; END LOOP; insert into delete_rows values (row_number); CLOSE table_name_cur; END; / 优点 : 只需一次全表扫描对回滚段要求最小出现 ora-1555 时, 回滚动作最少缺点 : 在中途仍然可能会出现 ora-1555 错误所要删除的数据在处理期间不能进行 update 操作这种方法还有另外一种形式 (bulk fetch delete): DECLARE TYPE ARRROWID IS TABLE OF ROWID INDEX BY BINARY_INTEGER; tbrows ARRROWID; v_rowid PLS_INTEGER; cursor cur_delete_table is select row_id from table_name where condition; BEGIN open cur_delete_table; loop fetch cur_delete_table bulk collect into tbrows limit 1000; FORALL v_rowid IN 1.. tbrows.count() DELETE table_name WHERE rowid = tbrows(v_rowid); exit when cur_delete_table%notfound; end loop; close cur_delete_table; END; / 二 expdp/impdp 方法需要停止应用

6 将所需数据导出, 再导入到新表中, 导入完成后, 将原表与新表分别 rename, 在新表上创建相关索引等对象使用导入导出方法需要停止业务对相关数据表的操作, 适用于保留数据小于需要清理的数据, 或者需要清理的表非常大的情况其优点是停止应用后不会出现 ora-1555 错误, 对回滚段需求小可先并行度新建索引状态最佳其缺点是需要额外的数据库空间与文件系统空间, 需要重新创建索引等表相关对象, 赋权等操作需要通过测试确定创建索引所需的 temp 排序空间三 CTAS 方法使用 create table new_tab as select * from origin_tab where condition; 过程中应用需要停止, 适用于保留数据小于需要清理的数据, 或者需要清理的表非常大的情况其优点是停止应用后不会出现 ora-1555 错误, 对回滚段需求小可选并行度与 nologging 新建索引状态最佳其缺点是需要额外的数据库空间, 需要手动创建新的索引等表相关对象, 赋权等操作需要通过测试确定创建索引所需的 temp 排序空间如果选择 nologging 选择, 在操作完成后, 建议立即进行数据库备份

7 作者个人简介林少杰 (kevinlin.ora) 是多维创新有限公司 ( 国内知名 Oracle 第三方服务 ) 上海地区的技术总监拥有 7 年 ORACLE 数据库系统的技术支持服务经验, 工作环境主要为 AIX Solaris 以及 HP 平台上的 Oracle 11g,Oracle 10g,Oracle 9i,Oracle 8i 集群环境所服务行业有电信行业金融服务业物流业等林少杰 (kevinlin.ora) 在数据库以及 SQL 层面的性能调优有丰富经验, 熟悉备份恢复数据库安装配置擅长异常问题的分析诊断林少杰 (kevinlin.ora) 一直以来遵循业界的最佳实践, 提供并实施高质量的解决方案, 不断坚持与增强技术服务的质量作为服务团队带头人曾参与以下项目 : 上海联通信息化部的核心 Oracle 数据库维护服务项目银联数据服务有限公司的数据库技术支持服务项目延边朝鲜族自治州住房公积金管理中心数据库技术支持服务项目上海浦东国际集装箱码头有限公司数据库技术支持服务项目银联数据服务有限公司的数据库技术支持服务项目上海联通信息化部的 BSS 核心 Oracle 数据库维护服务项目上海银商资讯公司的数据库技术支持服务项目

Oracle高级复制冲突解决机制的研究

Oracle高级复制冲突解决机制的研究 Oracle dbms_rectifier_diff Oracle : eygle (eygle.com@gmail.com dbms_rectifier_diff Oracle dbms_rectifier_diff : http://www.eygle.com/archives/2005/01/eoadbms_rectifi.html DIFFERENCES Oracle dbms_rectifier_diff.differences