czllfy
- 浏览: 108251 次
- 来自: ...
-
最新评论
表demo是重复拷贝自dba_objects，有88万左右，不重复的是27323，没有索引
方法一：delete from demo a where a.rowid <> (select max(rowid) from demo b where
b.object_id=a.object_id);
耗时：几个小时以上
方法一：delete from demo a where a.rowid <> (select max(rowid) from demo b where
b.object_id=a.object_id);
耗时：几个小时以上
方法二： delete from demo where rowid in
(select rid from
(select rowid rid,row_number() over(partition by object_id order by rowid) rn
from demo)
where rn <> 1 );
耗时：30秒
方法三： create table demo2 as
select object_id,owner... from
(select demo.*,row_number() over(partition by object_id order by rowid) rn from demo)
where rn = 1;
truncate table demo; insert into demo select * from demo2; drop table demo2;
共耗时： 10秒，适合大数据量的情况，产生更少回滚量；
分享到：
评论
2 楼
czllfy
2007-03-16
没有主键（Primary Key）约束保护的表格可能会让重复的数据行被插入进来。查找这种重复数据的传统方式是通过GROUP BY和HAVING关键字进行查询。在根据关键列把数据分组并计算每个组里的行数之后，有一个以上成员的组就是带有重复数据的组。
尽管发现这样的数据行很容易，但是解决这一问题却十分耗时。在Oracle里，独特的ROWID伪列（pseudocolumn）意味着没有两个列是真正一模一样的。你可以总是利用删除（DELETE）查询来参考一个以外的所有ROWID，以便删除所有的重复数据。这非常有效——如果你没有太多的重复数据需要删除的话。而Oracle 9i里引入的分析函数给予了我们一种更简单的方式来进行这种清除工作。
ROW_NUMBER()分析函数与ROWNUM伪列相似的地方在于它们都能够给输出的行编号。但是ROWNUM给出的是整个数据列完整的序列，而ROW_NUMBER会在我们在数据列里定义的每个分区里把编号重新设置回1。这样做的结果是不仅能够很容易就看到哪个组里有多个成员，还能够确切知道需要删除哪个行。
分析查询的格式是：
Functionname (arguments) OVER (PARTITION BY columns ORDER BY columns)
现在让我们假设在创建SCOTT.EMP表格副本的时候出现了错误，所有的行都被输入了两遍。尝试加入一个主键约束会失败，因为数据已经出现了重复。列表A显示了这一过程，为了清楚说明问题，它被分成两个阶段：
Listing A SQL> -- Will you just LOOK at this table? Lots of duplicates!
SQL>
SQL> SELECT empno, ename
2 FROM emp2
3 ORDER BY empno;
EMPNO ENAME
---------- ----------
7369 SMITH
7369 SMITH
7499 ALLEN
7499 ALLEN
7521 WARD
7521 WARD
7566 JONES
7566 JONES
7654 MARTIN
7654 MARTIN
7698 BLAKE
7698 BLAKE
7782 CLARK
7782 CLARK
7788 SCOTT
7788 SCOTT
7839 KING
7839 KING
7844 TURNER
7844 TURNER
7876 ADAMS
7876 ADAMS
7900 JAMES
7900 JAMES
7902 FORD
7902 FORD
7934 MILLER
7934 MILLER
28 rows selected.
SQL> -- First step: number the duplicates of each empno
SQL>
SQL> SELECT ROWID, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno) rn
2 FROM emp2;
ROWID RN
------------------ ----------
AAAM1UAAEAAAAGsAAA 1
AAAM1UAAEAAAAGuAAA 2
AAAM1UAAEAAAAGuAAB 1
AAAM1UAAEAAAAGsAAB 2
AAAM1UAAEAAAAGsAAC 1
AAAM1UAAEAAAAGuAAC 2
AAAM1UAAEAAAAGuAAD 1
AAAM1UAAEAAAAGsAAD 2
AAAM1UAAEAAAAGsAAE 1
AAAM1UAAEAAAAGuAAE 2
AAAM1UAAEAAAAGsAAF 1
AAAM1UAAEAAAAGuAAF 2
AAAM1UAAEAAAAGsAAG 1
AAAM1UAAEAAAAGuAAG 2
AAAM1UAAEAAAAGsAAH 1
AAAM1UAAEAAAAGuAAH 2
AAAM1UAAEAAAAGsAAI 1
AAAM1UAAEAAAAGuAAI 2
AAAM1UAAEAAAAGsAAJ 1
AAAM1UAAEAAAAGuAAJ 2
AAAM1UAAEAAAAGsAAK 1
AAAM1UAAEAAAAGuAAK 2
AAAM1UAAEAAAAGsAAL 1
AAAM1UAAEAAAAGuAAL 2
AAAM1UAAEAAAAGsAAM 1
AAAM1UAAEAAAAGuAAM 2
AAAM1UAAEAAAAGuAAN 1
AAAM1UAAEAAAAGsAAN 2
28 rows selected.
SQL> -- Now, use that as an inline view, and select just the dups
SQL> -- We're including the row number, it won't be in the final query
SQL>
SQL> SELECT ROWID, rn
2 FROM
3 (SELECT ROWID, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno) rn
4 FROM emp2)
5 WHERE rn > 1;
ROWID RN
------------------ ----------
AAAM1UAAEAAAAGuAAA 2
AAAM1UAAEAAAAGsAAB 2
AAAM1UAAEAAAAGuAAC 2
AAAM1UAAEAAAAGsAAD 2
AAAM1UAAEAAAAGuAAE 2
AAAM1UAAEAAAAGuAAF 2
AAAM1UAAEAAAAGuAAG 2
AAAM1UAAEAAAAGuAAH 2
AAAM1UAAEAAAAGuAAI 2
AAAM1UAAEAAAAGuAAJ 2
AAAM1UAAEAAAAGuAAK 2
AAAM1UAAEAAAAGuAAL 2
AAAM1UAAEAAAAGuAAM 2
AAAM1UAAEAAAAGsAAN 2
14 rows selected.
SQL> -- Now we DELETE all the rows in that set
SQL>
SQL> DELETE FROM emp2
2 WHERE ROWID IN
3 (SELECT ROWID
4 FROM (SELECT ROWID,
5 ROW_NUMBER() OVER (PARTITION BY empno ORDER BY EMPNO) rn
6 FROM emp2)
7 WHERE rn > 1);
14 rows deleted.
SQL> commit;
Commit complete.
SQL> -- Show the de-dup'ed table
SQL>
SQL> SELECT empno, ename
2 FROM emp2;
EMPNO ENAME
---------- ----------
7369 SMITH
7521 WARD
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7499 ALLEN
7566 JONES
7934 MILLER
首先是一个分析查询，通过empno行来分区；它使用ROW_NUMBER()给每个分区进行编号。如果没有重复的内容，分区就只有一个行，编号是“1”。但是，如果存在重复，那么它们就会被编上2、3等号码。这个查询还会返回我们用来唯一识别数据行的ROWID。第一个查询然后就被用作另外一个查询的内联视图，这第二个查询使用一个WHERE子句过滤掉“1”行，只返回重复的内容。最后，一个DELETE语句通过第二个查询使用IN操作符来删掉所有的重复内容。
就和所有的大规模DELETE一样，你需要记住的是，最好把想要保留的行（也就是说那些ROW_NUMBER为1的行）保存到一个新的表格里。INSERT所造成的负载要比DELETE小得多。
尽管发现这样的数据行很容易，但是解决这一问题却十分耗时。在Oracle里，独特的ROWID伪列（pseudocolumn）意味着没有两个列是真正一模一样的。你可以总是利用删除（DELETE）查询来参考一个以外的所有ROWID，以便删除所有的重复数据。这非常有效——如果你没有太多的重复数据需要删除的话。而Oracle 9i里引入的分析函数给予了我们一种更简单的方式来进行这种清除工作。
ROW_NUMBER()分析函数与ROWNUM伪列相似的地方在于它们都能够给输出的行编号。但是ROWNUM给出的是整个数据列完整的序列，而ROW_NUMBER会在我们在数据列里定义的每个分区里把编号重新设置回1。这样做的结果是不仅能够很容易就看到哪个组里有多个成员，还能够确切知道需要删除哪个行。
分析查询的格式是：
Functionname (arguments) OVER (PARTITION BY columns ORDER BY columns)
现在让我们假设在创建SCOTT.EMP表格副本的时候出现了错误，所有的行都被输入了两遍。尝试加入一个主键约束会失败，因为数据已经出现了重复。列表A显示了这一过程，为了清楚说明问题，它被分成两个阶段：
Listing A SQL> -- Will you just LOOK at this table? Lots of duplicates!
SQL>
SQL> SELECT empno, ename
2 FROM emp2
3 ORDER BY empno;
EMPNO ENAME
---------- ----------
7369 SMITH
7369 SMITH
7499 ALLEN
7499 ALLEN
7521 WARD
7521 WARD
7566 JONES
7566 JONES
7654 MARTIN
7654 MARTIN
7698 BLAKE
7698 BLAKE
7782 CLARK
7782 CLARK
7788 SCOTT
7788 SCOTT
7839 KING
7839 KING
7844 TURNER
7844 TURNER
7876 ADAMS
7876 ADAMS
7900 JAMES
7900 JAMES
7902 FORD
7902 FORD
7934 MILLER
7934 MILLER
28 rows selected.
SQL> -- First step: number the duplicates of each empno
SQL>
SQL> SELECT ROWID, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno) rn
2 FROM emp2;
ROWID RN
------------------ ----------
AAAM1UAAEAAAAGsAAA 1
AAAM1UAAEAAAAGuAAA 2
AAAM1UAAEAAAAGuAAB 1
AAAM1UAAEAAAAGsAAB 2
AAAM1UAAEAAAAGsAAC 1
AAAM1UAAEAAAAGuAAC 2
AAAM1UAAEAAAAGuAAD 1
AAAM1UAAEAAAAGsAAD 2
AAAM1UAAEAAAAGsAAE 1
AAAM1UAAEAAAAGuAAE 2
AAAM1UAAEAAAAGsAAF 1
AAAM1UAAEAAAAGuAAF 2
AAAM1UAAEAAAAGsAAG 1
AAAM1UAAEAAAAGuAAG 2
AAAM1UAAEAAAAGsAAH 1
AAAM1UAAEAAAAGuAAH 2
AAAM1UAAEAAAAGsAAI 1
AAAM1UAAEAAAAGuAAI 2
AAAM1UAAEAAAAGsAAJ 1
AAAM1UAAEAAAAGuAAJ 2
AAAM1UAAEAAAAGsAAK 1
AAAM1UAAEAAAAGuAAK 2
AAAM1UAAEAAAAGsAAL 1
AAAM1UAAEAAAAGuAAL 2
AAAM1UAAEAAAAGsAAM 1
AAAM1UAAEAAAAGuAAM 2
AAAM1UAAEAAAAGuAAN 1
AAAM1UAAEAAAAGsAAN 2
28 rows selected.
SQL> -- Now, use that as an inline view, and select just the dups
SQL> -- We're including the row number, it won't be in the final query
SQL>
SQL> SELECT ROWID, rn
2 FROM
3 (SELECT ROWID, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno) rn
4 FROM emp2)
5 WHERE rn > 1;
ROWID RN
------------------ ----------
AAAM1UAAEAAAAGuAAA 2
AAAM1UAAEAAAAGsAAB 2
AAAM1UAAEAAAAGuAAC 2
AAAM1UAAEAAAAGsAAD 2
AAAM1UAAEAAAAGuAAE 2
AAAM1UAAEAAAAGuAAF 2
AAAM1UAAEAAAAGuAAG 2
AAAM1UAAEAAAAGuAAH 2
AAAM1UAAEAAAAGuAAI 2
AAAM1UAAEAAAAGuAAJ 2
AAAM1UAAEAAAAGuAAK 2
AAAM1UAAEAAAAGuAAL 2
AAAM1UAAEAAAAGuAAM 2
AAAM1UAAEAAAAGsAAN 2
14 rows selected.
SQL> -- Now we DELETE all the rows in that set
SQL>
SQL> DELETE FROM emp2
2 WHERE ROWID IN
3 (SELECT ROWID
4 FROM (SELECT ROWID,
5 ROW_NUMBER() OVER (PARTITION BY empno ORDER BY EMPNO) rn
6 FROM emp2)
7 WHERE rn > 1);
14 rows deleted.
SQL> commit;
Commit complete.
SQL> -- Show the de-dup'ed table
SQL>
SQL> SELECT empno, ename
2 FROM emp2;
EMPNO ENAME
---------- ----------
7369 SMITH
7521 WARD
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7499 ALLEN
7566 JONES
7934 MILLER
首先是一个分析查询，通过empno行来分区；它使用ROW_NUMBER()给每个分区进行编号。如果没有重复的内容，分区就只有一个行，编号是“1”。但是，如果存在重复，那么它们就会被编上2、3等号码。这个查询还会返回我们用来唯一识别数据行的ROWID。第一个查询然后就被用作另外一个查询的内联视图，这第二个查询使用一个WHERE子句过滤掉“1”行，只返回重复的内容。最后，一个DELETE语句通过第二个查询使用IN操作符来删掉所有的重复内容。
就和所有的大规模DELETE一样，你需要记住的是，最好把想要保留的行（也就是说那些ROW_NUMBER为1的行）保存到一个新的表格里。INSERT所造成的负载要比DELETE小得多。
1 楼
yiding_he
2007-03-15
5555 好想了解怎么回事，不过看不懂，兄台能否讲解一下？
发表评论
文章已被作者锁定，不允许评论。
-
Oracle sql 性能优化调整
2009-11-05 08:26 4929Oracle sql 性能优化调整 1. 选用适合的ORA ... -
ORACLE SQL性能优化系列
2009-11-05 08:24 19781. 减少访问数据库的次数 当执行每条SQL语句时, ... -
Oracle中索引的使用 索引性能优化调整
2009-11-05 08:22 3944索引是由Oracle维护的可 ... -
oracle性能优化技巧
2009-11-05 08:22 12671.选用适合的ORACLE优化器 ORACLE的优化 ... -
Oracle性能优化的基本方法概述
2009-11-05 08:21 14321）设立合理的性能优化目标。 2）测量并记录当前性能。 3）确 ... -
查看oracle 某个表的所有字段名
2007-04-04 19:15 11275select column_name from u ... -
Oracle 序列的创建和使用
2007-03-30 21:52 16380创建序列 create sequence seq_a minv ... -
Oracle中插入图片并显示(用BLOB类型)
2007-03-30 21:43 6510要在oracle里面存入图片 用 blob类型 首先在数据 ... -
图片上传到oracle数据库的blob字段
2007-03-29 08:54 3380Ry_jbxx_list.jsp <%String h ... -
Oracle 函数大全
2007-03-15 20:42 896SQL中的单记录函数 1.ASCII 返回与指定的字符对应 ...
相关推荐
本文将探讨三种不同的方法来高效地删除`demo`表中的重复数据，并分析它们的执行时间和适用场景。 方法一： 该方法基于ROWID进行操作，通过保留每个对象_ID中的最大ROWID（最新插入的记录），删除其他ROWID。SQL语句...
这是一种高效的方法，但只适合删除少量重复数据。 示例代码： ``` delete from test_0210 where rowid not in (select max(rowid) from test_0210 group by name); ``` 这种方法的优点是效率高，直接定位到数据块...
- **解释**：这种方法通过创建一个新的无重复记录的表，然后清空原表并插入新表中的数据来完成重复数据的删除。 #### 四、注意事项 1. **备份**：在执行任何删除操作之前，请确保已经对数据进行了备份，以防意外...
根据给定文件的信息，本文将围绕“去除重复数据”这一主题进行深入探讨，重点解析一个简单易懂且适用性广的去重算法，并通过具体的代码示例来展示其实现过程。 ### 去除重复数据的基本概念 在计算机科学中，“去除...
### Oracle 下删除重复数据的方法及分析 #### 一、部分字段重复数据的处理 ##### 查询重复数据 在Oracle数据库中，经常会遇到表中存在部分字段重复的情况。为了找到这些重复的记录，我们可以使用以下SQL语句： `...
在IT领域，重复数据删除（Data Deduplication）是一种高效的数据存储策略，它通过识别并消除数据存储中的冗余信息来节省存储空间。本技术在云计算、备份、归档等领域广泛应用，尤其对于处理大量数据的场景，其效果尤...
重复数据删除技术是一种旨在减少冗余数据存储的技术。其基本原理是在数据写入或备份过程中识别并去除重复的数据副本，从而有效利用存储空间。这项技术广泛应用于数据中心、云计算环境和个人存储设备中，有助于降低...
在Oracle数据库中，删除重复数据是一项常见的数据清洗任务，尤其当表设计不当时，重复数据可能导致数据不一致和分析错误。以下将详细解释如何在Oracle中处理这两种类型的重复数据问题：部分字段重复和完全重复记录。...
本研究旨在探讨一种有效的方法来清理XML中的相似重复数据。通过这种方法，可以提高数据的质量，减少存储空间的浪费，并提升后续数据分析的准确性。这对于依赖高质量数据进行决策的企业和组织来说至关重要。 #### 三...
### Oracle删除表中的重复数据方法 在Oracle中删除表中的重复数据，可以采用多种策略，但通常涉及创建一个临时表来保存去重后的数据，然后用这个临时表覆盖原始表。这种方法可以避免直接修改原表带来的风险，确保...
### SQL删除表里重复数据的两种方法 在数据库管理中，数据的一致性和准确性是非常重要的。重复的数据不仅会占用额外的存储空间，还可能导致查询结果的不准确。因此，掌握如何有效地删除表中的重复记录是每个数据库...
* 哈希表算法：哈希表算法是一种高效的去除重复数据的算法，它使用哈希表来存储元素，然后删除重复的元素。 本例展示了如何合并两个链表并去除重复数据。这种技术可以应用于各种数据处理领域，例如数据库管理、数据...
固定长度数据块提供了一种较为简单的重复数据检测机制，但由于所有块大小相同，可能导致某些块内含有部分重复数据而无法被有效识别和删除。相比之下，可变长度数据段能够更精确地匹配重复数据，即使是在不同文件或...
Excel 删除重复数据行的方法和技巧 Excel 作为一个功能强大的电子表格处理软件，经常需要处理大量的数据。在实际工作中，我们常常会遇到重复数据的問題，这些重复数据不仅占用空间还会影响数据的分析和处理效率。...
综上所述，PDD算法是基于布隆过滤器的一种高级实现，专为高效处理数据流中的重复数据设计。它可能在Java环境中实现，尤其适用于处理地理数据。通过理解和应用这种算法，开发者能够有效地管理和分析大规模数据流，...
对于更复杂的操作或者需要频繁执行的任务，使用VBA（Visual Basic for Applications）编写宏代码是一种高效的方法。 #### VBA宏代码解析 下面是一段具体的VBA代码示例，用于删除指定工作表某列中的重复数据： ```...
本文提出了一种在云计算中实现高效加密数据重复删除的方法。该方法的核心在于引入了双线性映射和基于属性的代理重加密机制。双线性映射是一种数学工具，能够有效处理加密数据，而代理重加密允许用户在不直接共享私钥...
这里介绍一种非常高效的方法，它利用了Oracle特有的`ROWID`属性来实现。 **ROWID**：在Oracle数据库中，每个表行都有一个唯一的ROWID，它是表行的物理地址。通过比较ROWID，我们可以轻易地找出那些具有相同字段值但...