by kevinhu
定价系统运行一段时间后因为业务特性会产生大量的无效数据,需要定期清理这些无效数据以免影响系统数据库性能。千万以上大表清理推荐步骤:
1、查询数据现状
连接到:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 -
64bit Production
SQL> select count(*) from dj_floor_magin
where data_status=0;
COUNT(*)
----------
1141860
SQL> select count(*) from dj_floor_magin
where data_status=1; COUNT(*)
----------
137342
SQL>
SQL> select count(*) from
dj_room_element where data_status=0; COUNT(*)
----------
21517110
SQL> select count(*) from
dj_room_element where data_status=1; COUNT(*)
----------
407291
SQL>
以上面这两个表为例,可以看到data_status=1的有效数据很少,但是表内大量的已失效数据待清理。
下面是清理步骤(以 dj_room_element 举例)。
因为是千万级的数据,肯定不能直接delete,回把生产库搞死。因此选择转移有效数据后truncate表的方式。
如果某些情况下需要保留无效数据的话也可以按同样的思路操作,只是需要创建一个跟生成表一样的分区表而已。
-- 2、收集表信息
select
dbms_metadata.get_ddl('TABLE',upper('dj_room_element')) from
dual;
-- 收集索引信息
select * from user_indexes where
table_name=upper('dj_room_element');
-- 收集索引字段
select * from user_ind_columns where index_name =
upper('PK_ROOM_ELEMENTPROJECTID');
select * from user_ind_columns where index_name =
upper('PK_ROOM_ELEMENT');
--3、 创建临时表空表
create table tmp_dj_room_elementas select * from
dj_room_element where 1=2;
-- 4、转移有效数据
insert into tmp_dj_room_elementselect * from
dj_room_element where data_status=1;
-- 5、清空原表数据
truncate table dj_room_element;
-- 6、写回有效数据
insert into dj_room_elementselect * from
tmp_dj_room_element;
-- 7、清除临时表truncate table tmp_dj_room_element;
-- 8、检查恢复的数据
select count(*) from dj_room_element where
data_status=1;
select count(*) from dj_room_element where
data_status=0;
select count(*) from tmp_dj_room_element;
-- 9、数据清理完成后刷新统计信息和重建索引
-- 重新分析表创建统计信息
analyze table dj_room_element compute statistics;
-- 重建索引(分区表需要按分区重建)
alter index local_pk_room_element rebuild partition p1
online;
alter index local_pk_room_element rebuild partition p2
online;
alter index local_pk_room_element rebuild partition p3
online;
alter index local_pk_room_element rebuild partition p4
online;
alter index local_pk_room_element rebuild partition p5
online;
alter index local_pk_room_element rebuild partition p6
online;
alter index local_pk_room_element rebuild partition p7
online;
alter index local_pk_room_element rebuild partition p8
online;
alter index local_pk_room_element rebuild partition p9
online;
alter index local_pk_room_element rebuild partition p10
online;
alter index local_pk_room_element rebuild partition p11
online;
alter index local_pk_room_element rebuild partition p12
online;
alter index local_room_element_create_date rebuild
partition p1 online;
alter index local_room_element_create_date rebuild
partition p2 online;
alter index local_room_element_create_date rebuild
partition p3 online;
alter index local_room_element_create_date rebuild
partition p4 online;
alter index local_room_element_create_date rebuild
partition p5 online;
alter index local_room_element_create_date rebuild
partition p6 online;
alter index local_room_element_create_date rebuild
partition p7 online;
alter index local_room_element_create_date rebuild
partition p8 online;
alter index local_room_element_create_date rebuild
partition p9 online;
alter index local_room_element_create_date rebuild
partition p10 online;
alter index local_room_element_create_date rebuild
partition p11 online;
alter index local_room_element_create_date rebuild
partition p12 online;
-- 10、验证
-- create_date 是分区字段,自动使用分区
select * from dj_room_element where create_date between
add_months(trunc(sysdate, 'mm'), -1) and sysdate;
按f5获取查询分析结果
pl/sql
查询分析图1
-- room_id 是分区索引,自动使用分区索引
select * from dj_room_element where create_date between
add_months(trunc(sysdate, 'mm'), -1) and sysdateand
project_id='XXBGY' and batch=1 and round=1 and room_id like
'XXBGY%';
pl/sql查询分析图2
以上完成了从大表中清理大量无效数据,其他大表参考操作即可。
如需沟通讨论,可联系QQ:2622487640,本文版权归智扬信达所有,转载请注明出处。