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

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

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

SQL> select count(*) from syj_test_152;  COUNT(*)
----------
     10000SQL> select sysdate from dual;SYSDATE
--------------------
2006-8-29 15:39:26SQL> delete syj_test_152 a
  2  where rowid > (select min(rowid) from syj_test_152 where calling_nbr = a.calling_nbr);1529 rows deletedSQL> commit;Commit completeSQL> select sysdate from dual;SYSDATE
--------------------
2006-8-29 15:40:00SQL> select count(*) from syj_test_152;  COUNT(*)
----------
      8471

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

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

上面的这条语句对付小表可以，对付大表性能可能会有问题，因为内循环是逐条处理的。对付大数据量的表，有一种办法是：
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 #关键字 )原来我们计费系统中就是这么删除重复记录的，适用于大数据量，少量重复的情况。

create table tablea_bak
as
select distinct(a.*) from tableA a 然后drop以前那个表
最后rename tableA_bak to tableA最好在不用这张表的时候。

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

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

下面是我们以前用的一个删除重复记录的程序。
当时为了提高话单入库的速度，主键一开始是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;

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

调试易

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

解决方案 »