各位大虾:我客户有一个非常大的表,目前有1.5亿条记录,而且还在以每月两千万的速度增加。由于当时设计不够合理,目前这个表是没有主键,也没有分区的。
现在我要对其进行优化,分区是肯定要的了,但我首先得建立主键(四个字段的联合主键),但又由于数据错误,里面大概有一千万条重复记录,所以需要先删除这一千万条记录。
多次使用sql语句删除未能成功(数小时执行不完)
delete from table t where exist(select v.* from table v where <主键字段相等> and t.rowid<v.rowid)
后来就新建了一个表把重复记录的rowid 保存下来,有一千多万条记录,现在简单点了,直接匹配rowid删除。可两个巨大的表联结或者说匹配是个非常痛苦的事,每次执行还是遥遥无期。
特此向各位大虾求救!
现在我要对其进行优化,分区是肯定要的了,但我首先得建立主键(四个字段的联合主键),但又由于数据错误,里面大概有一千万条重复记录,所以需要先删除这一千万条记录。
多次使用sql语句删除未能成功(数小时执行不完)
delete from table t where exist(select v.* from table v where <主键字段相等> and t.rowid<v.rowid)
后来就新建了一个表把重复记录的rowid 保存下来,有一千多万条记录,现在简单点了,直接匹配rowid删除。可两个巨大的表联结或者说匹配是个非常痛苦的事,每次执行还是遥遥无期。
特此向各位大虾求救!
2.建立一张临时表,create table temp as select * from youtable.这个速度很快,是数据数据块的复制。
3.然后truncate table yourtable
4.修改你的yourtable的表结构
5.insert /*+append+/ into yourtable select * from temp---这个加上append也是属于数据块复制
6.drop table temp purge
弄个临时表,然后数据做出,用truncate table速度会快很多
DELETE TABLE_NAME WHERE ROWID NOT IN
(SELECT MAX (ROWID) FROM TABLE_NAME GROUP BY T1,T2,T3,T4) ;
COMMIT ;
1.5亿的表,如果仅仅使用一个delete语句删除的话,没有足够的SGA区和temp空间,效率会非常低。
可以使用游标删除,速度比较稳定,也比较快。
因为去掉重复数据需要先排序,排序的前提是把整个表的数据读一遍,
如果内存不够的话ORACLE会做DISK SORT
第二个原因就是DML的话COMMIT之前需要足够的UNDO空间和REDO LOG空间结果就是此次结束不了如果把你换成我的话,我会用下面的方法去做。1. 先导到一个CSV文件里
读表的时候可以考虑用并行处理的方式
生成CSV的时候你可以利用PL/SQL(网上也能下载得到)
2.把表truncate掉,再加上主键。
3.在利用sqlldr把数据导入到表里,当然这时候也可以考虑用并行处理的方法,
而且插入出现主键重复错误的的数据就放弃读入。注意一点就是要用并行处理的时候CPU个数不能少于三个,否则会变得更慢。以上方法里需要的程序,如果你弄不到的话,给我留个邮箱吧。
我给你发过去。
重复的数据应该是在同一个分区的.这样去重就可以在一个分区内做,减少处理的数量.挨个分区去重.
去完重后将原表rename成其他表,将新建的分区表rename成原表名.另外表是变动表么?还是一个月插入一次数据?
这么大一张表,费点时间肯定是要的了。最主要是的执行的稳定性。1000多万个重复数据,你都有rowid了,用个程序来一条条的删除吧。
将1000多万个rowid 保存在文本文件中,用个程序删除一条就将文本文件中的记录做个标记。
这样就算中途断了,你也可以继续。
A,创建一个表结构和你选择的customer一样的表结构,并且分好区,命名为T1
B,采用分区交换的方式:
ALTER TABLE T1 EXCHANGE PARTITION PART_NAME WITH TABLE CUSTOMER INCLUDING INDEXS WTIHOUT VALIDATION ;--后面的2个关键字是验证数据的有效性,如果加上的话则customer对于的数据必须在T1中拥有有效的分区;备注:分区交换比insert直接拉数据块,且不需要排序!
C,这样的话T1中现在有分区了,然后每个分区都进行删除重复数据!
delete from table t where exist(select v.* from table v where <主键字段相等> and t.rowid<v.rowid) and mod(xxx,40)=0如果是个生产表,那你先对需要的字段建立普通索引,写游标, 取mod 分通道40个甚至更多,处理!
1、删除重复的数据
2、建立分区表,相关的分区建立好
3、insert/*+append*/ into table_name nologging
select/*+parallel(table_name 16)*/ ....from ...where .....
4、建主键
在小机上估计要天把时间
先把前记录COPY到excel里面
用CONCATENATE()函数变成1,2,3,4,5,这样的然后用in删除
http://blog.csdn.net/huang_xw/archive/2011/03/06/6227106.aspx
2、用SQL将非重复数据筛选出来插入到临时表A
3、用SQL将重复数据筛选出来插入到另外一个临时表B
4、将临时表B的数据去重,
去重方法:insert into B as select XXX,rownum from table where rownum=1;然后将A表和B表合并 插入最终表
2.建立一张临时表,create table temp as select * from youtable.这个速度很快,是数据数据块的复制。(建临时表的时候去掉重复的,这个地方select比delete快)
3.然后truncate table yourtable
4.修改你的yourtable的表结构
5.insert /*+append+/ into yourtable select * from temp---这个加上append也是属于数据块复制
6.drop table temp purge
这种方法快