背景:
某定时应用程序(每天02:00启动)会读取指定目录下的所有txt扁平数据文件,并将数据保存到ORACLE数据库。本来应用程序将txt文件中数据入库后,会将目录中的txt数据文件备份到另外的目录中,但是应用程序有BUG导致备份失败。应用程序初始运行时需要对1亿全量的数据入库,接连运行8天,因应有程序有BUG导致数据重复入库8次,结果数据库表中有7亿重复数据。之所以把应用程序做成定时,是因为每天有100万增量数据需要入库。
表T_TEST结构:
col_id1 NUMBER(11)
col_id2 NUMBER(5)
col_3 VARCHAR2(32)
col_4 NUMBER(10)
col_5 VARCHAR2(256)
updatetime timestamp
注:通过col_id1,col_id2字段可以判断记录是否重复,updatetime为记录更新时间
问题:
最后更新的记录保留,将其它重复的记录删除
解决方案:
1.通过create table ... as select将不重复的记录重建成表T_TEST_1
create table T_TEST_1 nologging tablespace &tablespace_name as
select col_id1, col_id2, col_3, col_4, col_5
from (select col_id1,
col_id2,
col_3,
col_4,
col_5,
updatetime,
row_number() over(partition by col_id1, col_id2 order by updatetime desc) rn
from T_TEST)
where rn = 1
2.对新表重建索引,原表有多少索引,在新表上也重建多少索引
create index IND_T_TEST_1 on T_TEST_1(col_id1, col_id2)
nologging tablespace &ind_tablespace_name;
3.收集新表统计信息,确保SELECT查询采用正确高效率的执行计划
declare
BEGIN
dbms_stats.gather_table_stats(ownname => '&user',
tabname => 'T_TEST_1',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade => true,
method_opt => 'FOR ALL COLUMNS SIZE 1',
granularity => 'all');
END;
/
4.将新表和新索引更改为日志方式
alter table T_TEST_1 logging;
alter index IND_T_TEST_1 logging;
5.备份旧表,将新表切换上线
alter table T_TEST rename to T_TEST_BAK0902;
alter table T_TEST_1 rename to T_TEST;
不建议方案:
不建议直接在原表T_TEST上做DELETE操作
某定时应用程序(每天02:00启动)会读取指定目录下的所有txt扁平数据文件,并将数据保存到ORACLE数据库。本来应用程序将txt文件中数据入库后,会将目录中的txt数据文件备份到另外的目录中,但是应用程序有BUG导致备份失败。应用程序初始运行时需要对1亿全量的数据入库,接连运行8天,因应有程序有BUG导致数据重复入库8次,结果数据库表中有7亿重复数据。之所以把应用程序做成定时,是因为每天有100万增量数据需要入库。
表T_TEST结构:
col_id1 NUMBER(11)
col_id2 NUMBER(5)
col_3 VARCHAR2(32)
col_4 NUMBER(10)
col_5 VARCHAR2(256)
updatetime timestamp
注:通过col_id1,col_id2字段可以判断记录是否重复,updatetime为记录更新时间
问题:
最后更新的记录保留,将其它重复的记录删除
解决方案:
1.通过create table ... as select将不重复的记录重建成表T_TEST_1
create table T_TEST_1 nologging tablespace &tablespace_name as
select col_id1, col_id2, col_3, col_4, col_5
from (select col_id1,
col_id2,
col_3,
col_4,
col_5,
updatetime,
row_number() over(partition by col_id1, col_id2 order by updatetime desc) rn
from T_TEST)
where rn = 1
2.对新表重建索引,原表有多少索引,在新表上也重建多少索引
create index IND_T_TEST_1 on T_TEST_1(col_id1, col_id2)
nologging tablespace &ind_tablespace_name;
3.收集新表统计信息,确保SELECT查询采用正确高效率的执行计划
declare
BEGIN
dbms_stats.gather_table_stats(ownname => '&user',
tabname => 'T_TEST_1',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade => true,
method_opt => 'FOR ALL COLUMNS SIZE 1',
granularity => 'all');
END;
/
4.将新表和新索引更改为日志方式
alter table T_TEST_1 logging;
alter index IND_T_TEST_1 logging;
5.备份旧表,将新表切换上线
alter table T_TEST rename to T_TEST_BAK0902;
alter table T_TEST_1 rename to T_TEST;
不建议方案:
不建议直接在原表T_TEST上做DELETE操作