`
mark_java
  • 浏览: 16724 次
  • 性别: Icon_minigender_1
  • 来自: 新疆
社区版块
存档分类
最新评论

两种查询和删除重复记录的方法及其性能比较

ora10g 
阅读更多
这里我来给出两种查询和删除重复记录的方法，一种是使用rowid辅助完成的，另外一种是借助分析函数的力量来完成的。
这两种方法的执行效率相对其他方法是高效的。即便如此，这两种方法之间也有着本质上的性能区别，我将通过实验的方式给大家展示一下这两种方法，并道出其中的本质差别。

1.创建实验用表并初始化几条样本数据
sec@ora10g> create table t (x number, y varchar2(10));
sec@ora10g> insert into t values (1, 'sec');
sec@ora10g> insert into t values (2, 'Andy01');
sec@ora10g> insert into t values (2, 'Andy02');
sec@ora10g> insert into t values (3, 'Anna');
sec@ora10g> insert into t values (4, 'Anna');
sec@ora10g> insert into t values (5, 'John');
sec@ora10g> commit;
sec@secooler> analyze table t compute statistics for table for all indexes for all indexed columns;

Table analyzed.

sec@ora10g> select * from t;

         X Y
---------- --------------------
         1 sec
         2 Andy01
         2 Andy02
         3 Anna
         4 Anna
         5 John

6 rows selected.

2.第一种使用rowid辅助查询和删除重复记录的方法
1）查询重复记录
sec@ora10g> SELECT *
  2    FROM t t1
  3   WHERE t1.ROWID <> (SELECT MIN (t2.ROWID)
  4                        FROM t t2
  5                       WHERE t1.x = t2.x)
  6  /

         X Y
---------- --------------------
         2 Andy02

BTW：如果想要查询x和y字段同时重复的内容，可以在上面的子查询中再添加一个“AND t1.y = t2.y”条件即可。

2）删除重复记录
可以简单的将上面的查询语句改写成删除语句便可完成删除任务。
sec@ora10g> DELETE FROM t t1
  2        WHERE t1.ROWID <> (SELECT MIN (t2.ROWID)
  3                             FROM t t2
  4                            WHERE t1.x = t2.x)
  5  /

1 row deleted.

可以看到，此时x字段重复的内容已经被删除了。
sec@ora10g> select * from t;

         X Y
---------- --------------------
         1 sec
         2 Andy01
         3 Anna
         4 Anna
         5 John

3.第二种使用分析函数辅助查询和删除重复记录的方法
1）使用分析函数可以快速的定位重复记录的位置，下面结果中rn值大于1的行即表示重复行。
sec@ora10g> SELECT t1.x,
  2         t1.y,
  3         ROW_NUMBER () OVER (PARTITION BY t1.x ORDER BY t1.ROWID) rn
  4    FROM t t1
  5  /

         X Y                            RN
---------- -------------------- ----------
         1 sec                           1
         2 Andy01                        1
         2 Andy02                        2
         3 Anna                          1
         4 Anna                          1
         5 John                          1

6 rows selected.

2）进一步使用上面的rn结果作为辅助条件便可得到重复记录内容
sec@ora10g> SELECT t2.x, t2.y
  2    FROM (SELECT t1.x,
  3                 t1.y,
  4                 ROW_NUMBER () OVER (PARTITION BY t1.x ORDER BY t1.ROWID) rn
  5            FROM t t1) t2
  6   WHERE t2.rn > 1
  7  /

         X Y
---------- --------------------
         2 Andy02

3）删除方法
（1）第一种方法是利用rowid构造delete语句来完成删除，这种方法效率较低。
sec@ora10g> DELETE FROM t WHERE ROWID IN (
  2    SELECT rowid
  3      FROM (SELECT t1.x,
  4                   t1.y,
  5                   ROW_NUMBER () OVER (PARTITION BY t1.x ORDER BY t1.ROWID) rn
  6              FROM t t1) t2
  7     WHERE t2.rn > 1
  8  )
  9  /

1 row deleted.

（2）第二种方法，可以使用构造中间表t1的方法来完成，这是一种非常高效的去重方法，推荐在具有海量数据的数据库环境中使用。
sec@ora10g> create table t1 as
  2  SELECT t2.x, t2.y
  3    FROM (SELECT t1.x,
  4                 t1.y,
  5                 ROW_NUMBER () OVER (PARTITION BY t1.x ORDER BY t1.ROWID) rn
  6            FROM t t1) t2
  7   WHERE t2.rn = 1
  8  /

Table created.

sec@ora10g> drop table t;

Table dropped.

sec@ora10g> alter table t1 rename to t;

Table altered.

sec@ora10g> select * from t;

         X Y
---------- --------------------
         1 sec
         2 Andy01
         3 Anna
         4 Anna
         5 John

4.比较两种查询方法的执行计划，便可得到两种方法内在的性能差距的出处。
1）第一种使用rowid辅助查询的执行计划如下
sec@ora10g> set autot trace exp
sec@ora10g> SELECT *
  2    FROM t t1
  3   WHERE t1.ROWID <> (SELECT MIN (t2.ROWID)
  4                        FROM t t2
  5                       WHERE t1.x = t2.x)
  6  /

Execution Plan
----------------------------------------------------------
Plan hash value: 3924487551

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     5 |    55 |     6   (0)| 00:00:01 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL | T    |     6 |    66 |     3   (0)| 00:00:01 |
|   3 |   SORT AGGREGATE    |      |     1 |    11 |            |          |
|*  4 |    TABLE ACCESS FULL| T    |     1 |    11 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1".ROWID<> (SELECT MIN("T2".ROWID) FROM "T" "T2" WHERE
              "T2"."X"=:B1))
   4 - filter("T2"."X"=:B1)

2）第二种使用分析函数辅助查询的执行计划如下
sec@ora10g> SELECT t1.x,
  2         t1.y,
  3         ROW_NUMBER () OVER (PARTITION BY t1.x ORDER BY t1.ROWID) rn
  4    FROM t t1
  5  /

Execution Plan
----------------------------------------------------------
Plan hash value: 2335850315

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     6 |    66 |     4  (25)| 00:00:01 |
|   1 |  WINDOW SORT       |      |     6 |    66 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |     6 |    66 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

3）通过比较上面两个执行计划可以得到如下结论
第一种方法采用2次TABLE ACCESS FULL，第二种方法采用仅一次TABLE ACCESS FULL。
从执行计划上可以得出使用分析函数的方法更加的高效。

5.小结
在DBA数据库维护工作中，重复记录去除问题往往是不可避免的。在具有海量数据的数据库中去除重复记录是一件很艰巨的任务，如果方法选择不正确，很可能难以完成任务。
我这里介绍的两种方法都是相对比较高效的，细节之处请慢慢体会。

完成任务的手段和方法很多，只有将维护时间和对生产数据库的冲击较少到最低的方法才是可以接受的正确方法。

分享到：
| 移动互联
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    sql删除表中重复记录方法

    标题与描述概述的知识点是关于如何使用SQL语句来删除数据库表中的重复记录，这是一个在数据清理和维护数据完整性时非常实用的技术。以下是对给定文件中四种方法的详细解析和扩展，旨在帮助读者深入理解并掌握这些...

    经常面试到的SQL题(查询重复数据和清除重复数据).

    一种常见方法是创建一个新表，只包含唯一的记录，然后删除原始表并重命名新表。例如： ```sql CREATE TABLE employees_unique AS SELECT * FROM ( SELECT employee_id, MIN(employee_id) as min_id, name, ...

    MySQL 查询某个字段不重复的所有记录

    在MySQL中，当我们需要查询某个字段不重复的...在实际数据库管理中，理解如何有效地查询和处理重复记录对于优化数据库性能和提升用户体验至关重要。通过掌握这些技巧，我们可以编写出更高效、更适应业务需求的SQL查询。

    一种XML相似重复数据的清理方法研究

    本文提出了一种有效的XML相似重复数据清理方法，并详细介绍了基于树编辑距离的相似检测算法及其优化技术。这些方法和技术不仅能够帮助用户高效地清理XML数据中的重复项，还能显著提升数据质量，为后续的数据分析提供...

    重复数据删除的技术背景-白皮书

    重复数据删除可以分为两种主要类型：基于文件级别的重复数据删除和基于块级别的重复数据删除。 - **基于文件级别的重复数据删除**：这种类型的重复数据删除技术侧重于识别和删除整个文件的重复副本。 - **基于块...

    集合的概念及应用和HashSet保证数据不重复的原理

    例如，你可以用它来存储用户输入的唯一关键字，或者在处理数据库查询结果时去除重复记录。此外，HashSet也可以与其他集合类（如ArrayList和LinkedList）结合使用，实现更复杂的数据结构和算法。 总结来说，集合是...

    sql多表查询语句与方法

    GROUP BY zgy_jobs_faces.jname`，这个查询不仅通过WHERE子句筛选数据，还使用GROUP BY对jname字段进行分组，去除重复的jname值。 在实际应用中，多表查询通常结合WHERE子句、HAVING子句、聚合函数以及子查询来...

    oracle伪列以及伪表.docx

    这里给出了两种查找重复记录的方法：一种是通过GROUP BY和HAVING子句，另一种是利用ROWID的比较。删除重复记录时，可以使用MIN(ROWID)来保留每个组的第一个记录，删除其他重复项。 总之，Oracle的伪列和伪表是...

    ORACLE SQL性能調整

    UNION-ALL比UNION快，因为它不需要去除重复记录。在数据集确定无重复时，使用UNION-ALL可以提高查询效率。 #### 30. 使用提示(Hints) 在SQL语句中使用Hints可以指导优化器选择特定的执行计划，这对于复杂的查询...

    数据库加锁与性能分析

    解决这一问题通常有两种策略：一是提升事务隔离级别至Serializable，但这会牺牲性能；二是使用悲观锁，通过`SELECT ... FOR UPDATE`语句锁定要修改的数据，防止其他事务的并发修改。 不可重复读则涉及事务在不同...

    易语言双数组去重复数

    下面我们将详细探讨易语言中双数组去重复数的方法及其相关知识点。 首先，我们需要理解“双数组”的概念。在易语言中，双数组通常指的是两个关联的数组，它们可能在逻辑上有所关联，例如一个数组存储键（key），另...

    ORACLE PL-SQL面试题

    - 使用`EXISTS`子查询，通过比较同一表中的ROWID（行标识符）找出重复记录。ROWID是Oracle中唯一标识一行的物理地址。 - 使用`GROUP BY`和`HAVING COUNT(*) &gt; 1`结合`MAX(ROWID)`来找到每个组中最大ROWID的记录，...

    数据库 SQL语句 上实验课的时候做

    可以使用`WHERE`子句来设置条件，`GROUP BY`进行数据分组，`HAVING`过滤分组后的结果，`ORDER BY`进行排序，`DISTINCT`用于去除重复行。 4. **联接操作**：SQL允许通过`JOIN`语句将多个表的数据结合在一起，如`...

    SQL语句大全--实用

    - 在描述中提到了两种方法来复制表的结构，但不复制数据。方法一是使用`SELECT * INTO`语句，添加一个不可能满足的条件如`1&lt;&gt;1`，这样不会选择到任何记录。 - 方法二是`SELECT TOP 0 * INTO`，`TOP 0`确保没有记录...

    纯数数组去重复算法1千万3秒

    这样可以快速去除重复元素，但需要额外的内存空间。 2. **排序后去重**：对数组进行排序后，相邻的重复元素会集中在一起，通过遍历一次排序后的数组，只需保留第一个出现的元素即可。这种方法的时间复杂度是O(n log...

    DB2性能安全文件

    #### 十五、表数据重组（压缩）reorg命令的两种常用格式？ reorg命令有两种常用格式： 1. **简单格式**：`REORG TABLE YOUR_TABLE_NAME;` 2. **高级格式**：`REORG TABLE YOUR_TABLE_NAME WITH REORG RECOVER;` **...

    SQL语句优化

    虽然DISTINCT和ORDER BY能够去除重复记录并对结果排序，但在数据量大时，这两个操作会极大增加查询时间。如果可能，尽量避免或减少使用。 #### 七、子查询优化 当一个查询中包含多个子查询时，优化子查询的顺序...

    python列表练习-列表如何去重(不使用用set或者dict)（csdn）————程序.pdf

    总结一下，不使用set或dict的列表去重方法主要通过记录重复元素及其出现次数，然后根据这些信息从原列表中删除多余项来实现。虽然这种方法能够解决去重问题，但在处理大数据量时效率较低，不推荐在实际生产环境中...

    oracle（查询语句可能会有帮助）

    - 数据库分区是将大表分成较小、更易管理的部分，提高查询性能和维护效率。 - 常见的分区方式有范围分区、列表分区、哈希分区和复合分区。 6. 存储过程和函数： - 存储过程是一组预先编译的SQL语句，可封装复杂...

    MySQL单表查询

    - 作用：去除查询结果中的重复记录。 - 示例： ```sql SELECT DISTINCT gender FROM student; ``` **2. GROUP BY 与 HAVING 子句** - 作用：先根据指定的列对结果集进行分组，然后可进一步筛选这些组。 - ...

Magicbox
Global site tag (gtag.js) - Google Analytics