实例讲解临时处理去重 80w 数据时夯死现象

近日,在对一张百万数据的业务表进行去重时,去重操作竟然夯住了。下面就来简单回忆一下。

1、查询业务表数据量,查看到总共有200多w条

SQL> select count(*) from tb_bj_banker_etl;

2552381

2、查询表内应该去掉的重复数据量,共80多w条

SQL> select count(*) from tb_bj_banker_etl where (id) in (select id from tb_bj_banker_etl group by id having count(*)>1) and rowid not in(select max(rowid) from tb_bj_banker_etl group by id having count(*)>1);

830099

3、于是,在晚上下班前,执行了下面的语句脚本,为了去重

SQL> delete from tb_bj_banker_etl where(id) in (select id from tb_bj_banker_etl group by id having count(*)>1) and rowid not in(select max(rowid) from tb_bj_banker_etl group by id having count(*)>1);

SQL> commit;

4、第二天,到达现场时,发现PL/SQL Developer工具中昨天晚上执行的语句仍在执行中

首先察觉,80多w的去重数据跑了一个晚上也没跑完?这肯定是哪里出了问题?

怀疑有锁表。

于是查询是否有锁表的用户。

SELECT
 A.OWNER,            --OBJECT所属用户
 A.OBJECT_NAME,         --OBJECT名称
 B.XIDUSN,
 B.XIDSLOT,
 B.XIDSQN,
 B.SESSION_ID,          --锁表用户的session
 B.ORACLE_USERNAME,       --锁表用户的Oracle用户名
 B.OS_USER_NAME,         --锁表用户的操作系统登陆用户名
 B.PROCESS,
 B.LOCKED_MODE,
 C.MACHINE,           --锁表用户的计算机名称
 C.STATUS,            --锁表状态
 C.SERVER,
 C.SID,
 C.SERIAL#,
 C.PROGRAM            --锁表用户所用的数据库管理工具
FROM
 ALL_OBJECTS A,
 V$LOCKED_OBJECT B,
 SYS.GV_$SESSION C
WHERE
 A.OBJECT_ID = B.OBJECT_ID
 AND B.PROCESS = C.PROCESS
ORDER BY 1,2 

在下面结果中可以看到,锁表的只是去重语句的发起会话,并没有其它用户造成锁表,这说明语句仍然在执行嘛?带着疑问,开始尝试解决。

1 BJHYL tb_bj_banker_ETL 15 18 9000 913 BJHYL Administrator 4036:972 3 WORKGROUP\BACKDB ACTIVE DEDICATED 913 3381 plsqldev.exe

2 BJHYL tb_bj_banker_ETL 15 18 9000 913 BJHYL Administrator 4036:972 3 WORKGROUP\BACKDB INACTIVE DEDICATED 649 41791 plsqldev.exe

3 BJHYL tb_bj_banker_ETL 15 18 9000 913 BJHYL Administrator 4036:972 3 WORKGROUP\BACKDB INACTIVE DEDICATED 817 27777 plsqldev.exe

4 BJHYL tb_bj_banker_ETL 15 18 9000 913 BJHYL Administrator 4036:972 3 WORKGROUP\BACKDB INACTIVE DEDICATED 841 1981 plsqldev.exe

5、采用分批次,解决去重夯住问题

由于直接去重无法顺利进行,于是想到了分批次去重的方法,试一下。

第一次:
delete from tb_bj_banker_etl where(id) in (select id from tb_bj_banker_etl group by id having count(*)>1) and rowid not in(select max(rowid) from tb_bj_banker_etl group by id having count(*)>1) and rownum<=100000;
commit; 

第二次:
delete from tb_bj_banker_etl where(id) in (select id from tb_bj_banker_etl group by id having count(*)>1) and rowid not in(select max(rowid) from tb_bj_banker_etl group by id having count(*)>1) and rownum<=100000;
commit; 

。。。。。。。
。。。。。。。
。。。。。。。 

第八次:
delete from tb_bj_banker_etl where(id) in (select id from tb_bj_banker_etl group by id having count(*)>1) and rowid not in(select max(rowid) from tb_bj_banker_etl group by id having count(*)>1);
commit; 

结果:通过将80多万数据划分成以10w数据为单次进行去重操作,总共用时140多秒,完成了去重80万数据的目的。但为何直接处理出现夯死情况,有待后续跟踪分析。

以上就是临时处理去重80w数据时夯死现象的全部过程,希望可以帮到大家。

时间: 2015-09-01

SQL分组排序去重复的小实例

复制代码 代码如下: SELECT    *FROM    (        SELECT            userid,            classid,            remark,            ROW_NUMBER () OVER (                PARTITION BY userid,                classid            ORDER BY                addtime DESC        

浅谈sql数据库去重

关于sql去重,我简单谈一下自己的简介,如果各位有建议或有不明白的欢迎多多指出. 关于sql去重最常见的有两种方式:DISTINCT和ROW_NUMBER(),当然了ROW_NUMBER()除了去重还有很多其他比较重要的功能,一会我给大家简单说说我自己在实际中用到的. 假如有张UserInfo表,如下图:   现在我们要去掉完全重复的数据:SELECT DISTINCT * FROM dbo.UserInfo结果如下图: 但是现在有个新的需求,要把名字为'张三'的去重,也就是相同名字的只要一条数

SQL学习笔记五去重,给新加字段赋值的方法

去掉数据重复 增加两个字段 alter TABLE T_Employee Add FSubCompany VARchar(20); ALTER TABLE T_Employee ADD FDepartment VARCHAR(20); 给新加的字段赋值 UPDATE T_Employee SET FSubCompany='Beijing',FDepartment='Development' where FNumber='DEV001'; UPDATE T_Employee SET FSubCom

MySQL学习笔记之创建、删除、修改表的方法

本文实例讲述了MySQL学习笔记之创建.删除.修改表的方法.分享给大家供大家参考,具体如下: 创建表: create table users( id int, name varchar(64), sex bit(1), birthday date, Entry_date date, job varchar(32), salary float, resume text ); 1 添加列: alter table 表名 add 列名 数据类型 alter table users add image

springmvc学习笔记-返回json的日期格式问题的解决方法

springmvc学习笔记--json--返回json的日期格式问题 (一)输出json数据  springmvc中使用jackson-mapper-asl即可进行json输出,在配置上有几点: 1.使用mvc:annotation-driven 2.在依赖管理中添加jackson-mapper-asl <dependency> <groupId>org.codehaus.jackson</groupId> <artifactId>jackson-mappe

在CRUD操作中与业务无关的SQL字段赋值的方法

提高效率一直是个永恒的话题,编程中有一项也是可以提到效率的,那就是专注做一件事情,让其它没有强紧密联系的与之分开.这里分享下我们做CRUD时遇到的常见数据处理场景: •数据库表字段全部设计为非空,即使这个字段在业务上是可以为空的,之所以将数据库表字段全部设计为非空,这里有优点也有缺点,我们认为优点大于缺点,所以选择了它 优点: 1.获取值时,不用判断这个字段是否为null,直接可用于逻辑运算. 2.mysql DBA推荐此方案,可能是有利于性能,这里我并非求证过. 缺点: 1.业务含义没有nul

MongoDB学习笔记(五) MongoDB文件存取操作

由于MongoDB的文档结构为BJSON格式(BJSON全称:Binary JSON),而BJSON格式本身就支持保存二进制格式的数据,因此可以把文件的二进制格式的数据直接保存到MongoDB的文档结构中.但是由于一个BJSON的最大长度不能超过4M,所以限制了单个文档中能存入的最大文件不能超过4M.为了提供对大容量文件存取的支持,samus驱动提供了"GridFS"方式来支持,"GridFS"方式文件操作需要引入新的程序集"MongoDB.GridFS.

javascript学习笔记(五) Array 数组类型介绍

数组的创建 第一种: 复制代码 代码如下: var colors = new Array(); var colors = new Array(20);//创建包含20项的数组 var colors = new Array("Greg");//创建包含1项,即字符串"Greg"的数组 var colors = new Array("red","blue","green"); //创建包含3项 第二种: 复制代

SQL学习笔记三 select语句的各种形式小结

复制代码 代码如下: Select * from T_Employee select FName,FAge from T_Employee select FName from T_Employee where FSalary < 5000 select FName as 姓名,FAge as 年龄,FSalary as 月薪from T_Employee where FSalary < 5000 select FName as 姓名,FAge as 年龄,FSalary as 月薪,getda

SQL学习笔记一SQL基础知识

常用字段类型bit(0和1),datetime,int,varchar,nvarchar(可能含有中文用nvarchar) Varchar,nvarchar和char(n)的区别char(n)不足长度n的部分用空格填充. SQL语句中,字符串用单引号. SQL语句大小写不敏感.不敏感是指SQL的关键字,字符串值还是敏感的. 简单的INSERT语句 INSERT INTO Person(Id,Name,Age) VALUES(1,'Jim',20) 常用int和uniqueidentifier做主

SQL学习笔记四 聚合函数、排序方法

聚合函数 count,max,min,avg,sum... select count (*) from T_Employee select Max(FSalary) from T_Employee 排序 ASC升序 DESC降序 select * from T_Employee order by Fage 先按年龄降序排列.如果年龄相同,则按薪水升序排列 select * from T_Employee order by FAge DESC,FSalary ASC order by 要放在 wh

python网络编程学习笔记(五):socket的一些补充

1.半开放socket 利用shutdown()函数使socket双向数据传输变为单向数据传输.shutdown()需要一个单独的参数,该参数表示了如何关闭socket.具体为:0表示禁止将来读:1 表示禁止将来写:2表示禁止将来读和写. 2.timeouts控制超时 调用socket的settimeout()函数,向其传递参数,表明超时时间设置.当访问一个socket,如果经过了参数设定的时间后,什么都没有发生,则会产生一个socket.timeout异常.例如:当程序运行后,会等待数据传入.