时间戳

首页 > 解决方案 > 从包含数万亿条记录的 Oracle 表中删除数十亿条记录

oracle - 从包含数万亿条记录的 Oracle 表中删除数十亿条记录

问题描述

我需要删除大约。来自具有大约 100 + 十亿行的 Oracle 表的 300 亿行。我有我需要在临时表中删除的行的所有 ID。目前我正在使用如下的单个删除语句，也使用 SUBPARTITION 并在 temp_table 上创建索引。然而，这需要 4 个多小时才能在 PRODUCTION 中完成。

DELETE FROM table_name SUBPARTITION(subpartition_name) WHERE id IN (SELECT id FROM temp_table);
COMMIT;

有没有办法可以优化它以使其运行得更快。

只是为了说明：

  1. 我所指的 oracle 表对于多个客户端来说是通用的，所以下面的选项在这里不合适。创建新表并将所需数据移入其中并删除旧表，然后将新表重命名为旧表。
  2. 批量删除：遍历临时表并删除类似下面的内容，在非生产环境中需要更多时间，并且不确定它在生产环境中的情况。
DECLARE
  vCT NUMBER(38) := 0;

BEGIN
  FOR t IN (SELECT id FROM temp_table) LOOP
    DELETE FROM table_name WHERE id = t.id;
    ......
    ......
      COMMIT;
    END IF;
  END LOOP;
  COMMIT;
END;
  1. 在这里创建单独的 DELETE 语句的选项也不可行，因为记录数以十亿为单位。

我确实检查过，我正在使用它的表上有分区和子分区，并且没有依赖它的子表。

请建议。

标签： oracle

解决方案

删除（或更新）大量行很痛苦并且需要很多时间。

管理它的最有效方法是使用可更新的连接视图

要求但应该没有问题的是增量表上包含要删除的唯一索引。id

此外，在使用这种方法时，请确保大表在该列上没有唯一索引id。如果两个表在连接列上都有唯一索引，则会出现一些技术问题- 见下文。

比使用以下查询（如果您想明智地使用子分区）删除

delete from 
(
select delta.id, big.id big_id 
from delta 
join big subpartition (SYS_SUBP220880)
on delta.id = big.id  
)

Oracle 使用两个表的哈希联接，在您的情况下，这是管理您的规模的唯一可能性。您可以部署并行选项，不要忘记启用它。

这是预期的执行计划：

-------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT              |       |   100K|  1757K|   875   (2)| 00:00:01 |       |       |
|   1 |  DELETE                       | BIG   |       |       |            |          |       |       |
|*  2 |   HASH JOIN                   |       |   100K|  1757K|   875   (2)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS FULL          | DELTA |   100K|   488K|    47   (3)| 00:00:01 |       |       |
|   4 |    PARTITION COMBINED ITERATOR|       |   783K|  9947K|   825   (1)| 00:00:01 |   KEY |   KEY |
|   5 |     TABLE ACCESS FULL         | BIG   |   783K|  9947K|   825   (1)| 00:00:01 |    65 |    65 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("DELTA"."ID"="BIG"."ID")

注意 - 如果有多个连接表被保留，请参阅文档Note on Updatable Views

对于 DELETE 语句，如果连接导致多个保留键的表，则 Oracle 数据库将从 FROM 子句中命名的第一个表中删除，无论视图是否是用 WITH CHECK OPTION 创建的。

推荐阅读