在oralce中如何一次性清除重复记录(重复记录仅保留一条),而且效率较高!谢谢指教!

[aivii]

请问:
在oralce中如何一次性清除重复记录(重复记录仅保留一条),
而且效率较高!谢谢指教!

[yown]

delete from tbl where id not in(select max(id) from tbl group by col1,col2)

[xiaoxiao1984]

delete from tab_name a where rowid > (select min(rowid) from tab_name b where a.关键字 = b.关键字 group by 关键字)

[swimming8243]

ls的肯定可以吗？
rowid > select min(rowid) 是什么意思呢？

[act_as_your_wish]

SQL> select count(*) from syj_test_152;

COUNT(*)
----------
10000

SQL> select sysdate from dual;

SYSDATE
--------------------
2006-8-29 15:39:26

SQL> delete syj_test_152 a
2 where rowid > (select min(rowid) from syj_test_152 where calling_nbr = a.calling_nbr);

1529 rows deleted

SQL> commit;

Commit complete

SQL> select sysdate from dual;

SYSDATE
--------------------
2006-8-29 15:40:00

SQL> select count(*) from syj_test_152;

COUNT(*)
----------
8471

[act_as_your_wish]

不好意思，刚没注意，已经有人答了，就是 xiaoxiao1984(笨猫儿^_^) 的意思！

[xiaoxiao1984]

当出现重复记录的时候，保留最小rowid的记录

[gechangwei]

上面的这条语句对付小表可以，对付大表性能可能会有问题，因为内循环是逐条处理的。

对付大数据量的表，有一种办法是：
alter table xx enable primary key xxxx

然后再用类似下列的语句删除：
DELETE #表名 WHERE ROWID IN(
SELECT ROW_ID FROM #异常表
WHERE TABLE_NAME = ''#表名''
MINUS
SELECT MIN(ROWID) FROM #表名
WHERE ROWID IN
(SELECT ROW_ID FROM #异常表
WHERE TABLE_NAME = ''#表名'')
GROUP BY #关键字 )

原来我们计费系统中就是这么删除重复记录的，适用于大数据量，少量重复的情况。

[act_as_your_wish]

兄弟：能不能给个例子？我们这捡重是预处理的任务，根据局方要求，有些局方要求不能捡重！

[act_as_your_wish]

帅哥：把那些中文换成可以执行的表名就行了，只要你那边能执行就行了，就是没明白你写的语句的意思，才疏学浅，没办法！

[hugh_9]

create table tablea_bak
as
select distinct(a.*) from tableA a

然后drop以前那个表
最后rename tableA_bak to tableA

最好在不用这张表的时候。

[wiler]

delete from tab_name a where rowid > (select min(rowid) from tab_name b where a.重复列 = b.重复列)

[wiler]

oracle的SQL脚本优化有一条是这样讲的
通过rowid检索是所有检索方式中最快的
所以上面给的语句是最快的

[gechangwei]

下面是我们以前用的一个删除重复记录的程序。
当时为了提高话单入库的速度，主键一开始是disable掉的，在正式汇总之前，会进行删重操作，即enable primary key。

用alter table xx enable primary key 时有exception参数将发现的重复记录插入exceptions表（需要自己建，格式参见资料）。

这个程序看似复杂，但却是oracle工程师和我们花了不少时间写出来的，因为我们每张表数据有几千万，但重复记录数量一般不多，处理性能比较关键。以下程序适合这种情况，不过未必适合你们的情况。



/* =============================================================== *
GET_PK：取得主键定义
说明：
如果返回NULL，表示这个表没有定义主键
* =============================================================== */
FUNCTION GET_PK(p_table CHAR) RETURN CHAR IS
CURSOR c_col(p_constraint CHAR) IS
SELECT COLUMN_NAME
FROM USER_CONS_COLUMNS
WHERE CONSTRAINT_NAME = p_constraint
ORDER BY POSITION;

v_constraint VARCHAR2(30);
v_pk VARCHAR2(60); --主键定义，字段间以逗号分割
BEGIN
/* 取主键名称 */
BEGIN
SELECT CONSTRAINT_NAME
INTO v_constraint
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = UPPER(p_table) AND
CONSTRAINT_TYPE = 'P';
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;

/* 取出主键字段 */
v_pk := '';
FOR rec IN c_col(v_constraint) LOOP
v_pk := v_pk || rec.column_name || ',';
END LOOP;
v_pk := SUBSTR(v_pk,1,LENGTH(v_pk)-1);

RETURN v_pk;
END GET_PK;


/* =============================================================== *
EXIST_PK：判断主键是否已经存在
说明：
如果返回NULL，表示这个表没有定义主键
* =============================================================== */
FUNCTION EXIST_PK(p_table CHAR) RETURN BOOLEAN IS
v_constraint VARCHAR2(30);
v_count NUMBER(2);
BEGIN
/* 取主键名称 */
BEGIN
SELECT CONSTRAINT_NAME
INTO v_constraint
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = UPPER(p_table) AND
CONSTRAINT_TYPE = 'P';
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;

/* 判断主键是否已经存在 */
SELECT COUNT(*)
INTO v_count
FROM USER_INDEXES
WHERE INDEX_NAME = v_constraint;

IF v_count = 0 THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END EXIST_PK;


/* =============================================================== *
ENABLE_PK：ENABLE主键
说明：
1.返回值含义：
-3：没有定义主键
-2：主键已经存在
-1：重建主键失败
0：建主键成功，没有重复记录
n(n>0)：建主键成功，重复记录数为n
* =============================================================== */
FUNCTION ENABLE_PK(p_table CHAR,p_storage CHAR,p_exception CHAR)
RETURN NUMBER
IS
v_exist BOOLEAN;
v_SQL VARCHAR2(2000);
v_RowNum NUMBER(10);
v_return NUMBER(10);
v_pk VARCHAR2(60);
e_fail EXCEPTION;
PRAGMA EXCEPTION_INIT(e_fail,-2437);
v_Delete VARCHAR2(1000) :=
'DELETE #表名 WHERE ROWID IN(
SELECT ROW_ID FROM #异常表
WHERE TABLE_NAME = ''#表名''
MINUS
SELECT MIN(ROWID) FROM #表名
WHERE ROWID IN
(SELECT ROW_ID FROM #异常表
WHERE TABLE_NAME = ''#表名'')
GROUP BY #关键字 )';
BEGIN
/* 如果主键已经存在，那么返回 */
v_exist := EXIST_PK(p_table);
IF v_exist IS NULL THEN
RETURN -3;
ELSIF v_exist = TRUE THEN
RETURN -2;
END IF;

/* enable 主键 */
BEGIN
v_SQL := 'ALTER TABLE '||p_table||' ENABLE PRIMARY KEY '||
p_storage ||' EXCEPTIONS INTO '||p_exception;
v_RowNum := EXEC_SQL(v_SQL);
RETURN 0;
EXCEPTION
WHEN e_fail THEN -- 有重复记录
/* 删除重复记录 */
v_SQL := Replace(v_Delete,'#表名',p_table);
v_SQL := Replace(v_SQL,'#异常表',p_exception);
v_pk := GET_PK(p_table);
v_SQL := REPLACE(v_SQL,'#关键字',v_pk);
v_return := EXEC_SQL(v_SQL);
v_SQL := 'DELETE #异常表 WHERE TABLE_NAME = '''||UPPER(p_table)||'''';
v_RowNum := EXEC_SQL(REPLACE(v_SQL,'#异常表',p_exception));
PUB_TOOL.EXPLAIN_PLAN('3',v_SQL);
COMMIT;

/* enable 主键 */
v_SQL := 'ALTER TABLE '||p_table||' ENABLE PRIMARY KEY '||p_storage;
v_RowNum := EXEC_SQL(v_SQL);
PUB_TOOL.EXPLAIN_PLAN('4',v_SQL);
RETURN v_return;
END;

EXCEPTION
WHEN OTHERS THEN
RETURN -1;
END ENABLE_PK;

[act_as_your_wish]

我们有时候一个月的话单量有九位数，拣重都是通过CS端的程序在预处理的时候实现的，不用存储过程来做这么大的操作。等有空的时候看看你们是怎么实现的，现在还没空，有什么问题等下再请教！

[xiaoxiao1984]

delete from tab_name a where rowid > (select min(rowid) from tab_name b where a.关键字 = b.关键字 group by 关键字)
这个sql语句的效率不低啊？
楼主执行起来很慢么？

[act_as_your_wish]

不是这个意思，数量小的时候用这个可以，数据量大的时候可能就要换一种思路了！