如何快速删除数百万行的数据

发表于 分类于 ， 阅读次数： 本文字数： 4.7k 阅读时长 ≈ 17 分钟
在Oracle中如何快速删除数百万行的数据，delete，truncate，drop该如何选择？

参考链接

  • 主要内容翻译自Chris Saxon | Oracle Blogs

前言

删除大量行可能会很慢。 而且有可能需要更长的时间，因为另一个会话已锁定您要删除的数据。

幸运的是，有一个技巧可以加快这个过程：

将 DML 转换为 DDL！

在这篇文章中，我们将首先快速回顾一下[删除的工作原理](#How to Delete Rows with SQL)。 然后查看可在 Oracle 数据库中使用的几种替代方法来更快地删除行：

  • [Removing all the rows fast with truncate](#Delete all the Rows Fast with Truncate)
  • [Using create-table-as-select to wipe a large fraction of the data](#Remove Rows with Create-Table-as-Select)
  • [Dropping or truncating partitions](#Delete All Rows in a Partition Fast)
  • [Using a filtered table move](#Delete Data with a Filtered Table Move)

How to Delete Rows with SQL

删除行很容易。

使用delete语句。 这列出了您要从中删除行的表。 确保添加一个 where 子句来标识要擦除的数据，否则您将删除所有行！

1
2
delete from table_to_remove_data
where  rows_to_remove = 'Y';

但当您确实想要删除数据时，删除操作可能需要一段时间。 特别是当您要清除表中的大部分行时。 那么怎样才能让删除速度更快呢？ 是时候将 SQL 语句切换为 DDL 了。 让我们从最简单的情况开始：清空表中的所有数据。

Delete操作和系统资源

在delete操作中，SQL语句首先要扫描表或者索引，以找到符合条件的记录，然后再将它们删除。这个过程将消耗大量的CPU资源和产生大量IO，同时还会产生大量的UNDO数据。

下面做实验看看delete，drop，truncate之间的区别：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
SYS@orcl> create table t1 as select * from dba_objects;

Table created.

SYS@orcl> exec dbms_stats.gather_table_stats(user,'t1');

PL/SQL procedure successfully completed.

SYS@orcl> set autotrace trace exp stat;
SYS@orcl> delete from t1 where object_id<10000;

9811 rows deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 775918519

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |      |  9407 | 47035 |   339   (1)| 00:00:05 |
|   1 |  DELETE            | T1   |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |  9407 | 47035 |   339   (1)| 00:00:05 |
---------------------------------------------------------------------------

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

   2 - filter("OBJECT_ID"<10000)


Statistics
----------------------------------------------------------
         38  recursive calls
      10789  db block gets
       1279  consistent gets
          0  physical reads
    3632060  redo size
        836  bytes sent via SQL*Net to client
        793  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
       9811  rows processed

如上所示，delete操作产生了10000+数据块读取（db blocl gets+consistent gets)，同时产生了3MB+的重做日志。其唯一的好处就是可以rollback

如下，即使创建并使用了索引，也避免不了这种消耗

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
SYS@orcl> create index idx_t1_id on t1(object_id);

Index created.

SYS@orcl> exec dbms_stats.gather_index_stats(user,'idx_t1_id');

PL/SQL procedure successfully completed.

SYS@orcl> delete from t1 where object_id<10000;

9811 rows deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 4206545880

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |           |  9407 | 47035 |    22   (0)| 00:00:01 |
|   1 |  DELETE           | T1        |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T1_ID |  9407 | 47035 |    22   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"<10000)


Statistics
----------------------------------------------------------
         41  recursive calls
      10799  db block gets
         49  consistent gets
          0  physical reads
    3794052  redo size
        839  bytes sent via SQL*Net to client
        793  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
       9811  rows processed

但是对于truncate,drop,分区来说消耗就会小很多：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- 将删除条件 object_id<10000作为分区的界限来创建一个分区表
SYS@orcl> ed 1.sql

SYS@orcl> ! cat 1.sql
create table t1_part(object_id int,object_name varchar2(1000)) partition by range(object_id)
(
partition p1 values less than(10000),
partition p2 values less than(20000),
partition p3 values less than(30000),
partition p4 values less than(40000),
partition pm values less than(maxvalue)
);

SYS@orcl> @1.sql

Table created.

SYS@orcl> insert into t1_part select * from t1;

87007 rows created.

SYS@orcl> commit;

Commit complete.

SYS@orcl> select count(1) from t1_part partition(p1);

  COUNT(1)
----------
      9811

SYS@orcl>

可以使用sql_trace工具来获取truncate,drop等DDL操作的相关信息，下面分别对P1分区进行truncate和drop操作：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
SYS@orcl> alter session set sql_trace=true;

Session altered.

SYS@orcl> alter table t1_part truncate partition p1;

Table truncated.

SYS@orcl> alter session set sql_trace=false;

Session altered.

SYS@orcl> truncate table t1_part;

Table truncated.

SYS@orcl> insert into t1_part select * from t1;

87007 rows created.

SYS@orcl> commit;

Commit complete.

SYS@orcl> alter session set sql_trace=true;

Session altered.

SYS@orcl> alter table t1_part drop partition p1;

Table altered.

SYS@orcl> alter session set sql_trace=false;

Session altered.

查看trace文件，使用tkprof处理后可以发现，对分区操作消耗的资源远远小于DML操作。

DDL操作消耗资源主要针对数据字典的修改，这个值基本上是确定的；而DML操作会随着数据量的增加，消耗的资源也会相应增加。

DELETE与释放空间

delete操作并不能释放空间，也就是说，删除了哪个表的数据，腾出空间还是只能用于那个表，并不能给其他对象使用，这是因为delete操作并不能让表的高水位线下降。而truncate或drop操作则可以释放空间来给其他对象使用。

可通过select count(*) from user_extents where segment_name='T1';来观察这一现象。对于delete操作其extents并不会发生变化。

什么情况下使用delete

delete仅适用于删除少量的数据，并且还需要在有索引的情况下使用。如果没有索引，即便要删除的数据很少，依然需要全表扫描（分区表除外）。

Delete all the Rows Fast with Truncate

如果要擦除表中的所有数据，最快、最简单的方法是使用truncate：

1
truncate table to_empty_it;

这是即时元数据操作。 这也将==重置表的高水位线==。 默认情况下，它还会释放表的 minextent 之上的所有空间。 您可以使用存储子句更改此行为：

  • truncate table ... reuse storage 保留分配给表的所有空间
  • truncate table ... drop storage （默认）释放表的 minextents 之上的所有空间
  • truncate table ... drop all storage 释放表中的所有空间

在使用 truncate 之前，您还需要检查是否有任何外键指向该表。 如果有，即使子表为空 ，truncate 也会抛出以下错误：

1
2
truncate table to_empty_it;
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

为了解决这个问题，您需要首先禁用引用该表的外键。 然后重新启用它们。 例如：

1
2
3
4
5
6
7
alter table to_empty_it_child 
  modify constraint fk disable;

truncate table to_empty_it;

alter table to_empty_it_child 
  modify constraint fk enable;

当然，子表也必须为空。 如果其中有行，则无法重新验证外键！ 如果您想一次性清除父表和子表，Oracle Database 12c 中有一个技巧。 添加级联子句：

1
2
truncate table to_empty_it
  cascade;

注意：级联选项将从表中删除所有行。 以及引用它的子表。 这是一个以空数据库结束的简单方法！ 执行此操作时要格外小心。

幸运的是，您==需要将外键声明为删除级联==才能正常工作。 这是一个罕见的选择。 所以你不太可能做到这一点。 确保在运行级联截断之前仔细检查！ 但是删除表中的所有内容是不寻常的。 通常您想要删除行的子集。 您还可以使用其他几种 DDL 技巧来加快速度。 最广泛使用的是将您想要保留的行保存在临时表中。 然后把数据切换过来。

Remove Rows with Create-Table-as-Select

在表中插入行比删除行更快。 使用 create-table-as-select (CTAS) 将数据加载到新表中的速度更快。 因此，如果您要从表中删除大部分行，您可以：

  • Create a new table saving the rows you want to keep
  • Truncate the original table
  • Load the saved rows back in with insert as select

示例：

1
2
3
4
5
6
7
8
create table rows_to_keep
  select * from massive_table
  where  save_these = 'Y';

truncate table massive_table;

insert into massive_table
  select * from rows_to_keep;

或者该技术还有另一个版本，速度甚至更快。

Switch the tables over，把桌子翻过来。

其过程类似：

  • Create a new table saving the rows you want to keep
  • Drop or rename the original table
  • Rename the new table to the original
1
2
3
4
5
6
7
8
9
create table rows_to_keep
  select * from massive_table
  where  save_these = 'Y';

rename massive_table
  to massive_archived;

rename rows_to_keep
  to massive_table;

这只会加载一次数据。 因此，比使用 truncate + insert （如前面的方法）更快。 ==要完成切换，您还需要将所有索引、约束、授权等从旧表复制到新表。 这些步骤可能需要很长时间。 因此，您需要在表上进行测试，看看哪种 CTAS 方法最快==。 您也更有可能在复制依赖项时犯错误。 但这两种选择都很棘手。 并且有一个巨大的缺点： 您需要使应用程序离线才能安全地执行这些操作。 这使得 CTAS 方法不适用于许多场景。 幸运的是，Oracle 数据库还有一些其他可用的技巧。

Delete All Rows in a Partition Fast

当您对表进行分区时，您在逻辑上将其分为许多子表。 然后，您可以执行仅影响单个分区中的行的操作。 这提供了一种简单、快速的方法来删除分区中的所有行。 删除或截断它！

1
2
3
4
5
alter table to_archive_from
  drop partition to_remove_it;

alter table to_archive_from
  truncate partition to_empty_it;

与常规截断一样，您==需要先禁用子表上的外键==，然后才能截断分区。 要使用此方法，必须满足以下条件：

  • The table is partitioned
  • You want to remove all the rows in a partition

所以最大的问题是： 是否值得对表进行分区以使删除速度更快？ 要回答这个问题，您首先需要提出一个后续问题： 您想定期重复此删除吗？==如果这是一次性清理，那么首先对表进行分区几乎没有什么好处。 但对于定期删除数据来说，这可以有很大帮助。== 例如，信息生命周期管理流程。 通过此功能，您可以将最旧的数据存档到另一个表、数据库或存储系统。 如果您已在插入日期对表进行分区，则可以轻松擦除最旧分区中的数据。 但请记住：对表进行分区会影响针对该表的所有操作。 按日期分区可能会使归档过程变得简单而快速，但也可能会使针对表的关键查询变慢。 在开始分区之前，先根据表测试您的整个应用程序工作负载！ 您可能会发现对其他 SQL 语句的影响使得许多表无法进行分区。

幸运的是，Oracle Database 12c 第 2 版添加了另一种快速删除大量数据的方法： A filtered table move(过滤表移动)。

Delete Data with a Filtered Table Move

通常，您使用 alter table ... move 来更改存储行的表空间。或者表的其他物理属性，例如压缩设置。 通过过滤表移动，您可以迁移数据的子集。 通过在语句中添加一个 where 子句来做到这一点：

1
2
3
alter table to_delete_from
  move including rows
  where rows_to_keep = 'Y';

这只会移动与 where 子句匹配的行。 如果您想要删除大量数据，这可能比删除快得多。 它有一个online子句。 因此，与 CTAS 方法不同，您==可以在应用程序仍在运行时执行此操作==。 不过，与截断一样，如果启用了指向该表的外键，它将失败并显示 ORA-02266。 所以这需要仔细规划在父表上使用。 或者应用程序中断。

删除大量数据最快的方法是什么？

那么，最重要的问题是：哪种方法最快？ 为了找到答案，我加载了一个包含 100,000 行的表。 然后测量删除 90%、50% 和 10% 的数据需要多长时间。 除了分区比较之外，测试都是在非分区表上进行的。 分区表被分成 10,000 行的范围。 因此测试分别截断了 9、5 和 1 个分区。 该图显示了研究结果：

img

  • 常规delete无疑是最慢的操作。 删除 90% 的数据时平均需要 11 秒。 仅当删除少至 10% 的行时，它才优于任何 DDL 方法。 即使如此，它也具有与许多 DDL 方法相似的运行时。 因此，在删除相对较少的行时，值得检查是否有比删除更快的选项。
  • 截断分区可以提供最佳的整体性能。 尽管在删除大部分数据时比其他 DDL 方法稍微慢一些。 如果您想安排定期删除，这将是一个不错的选择。 例如，删除最旧月份的数据。 但分区还有其他含义。 将其添加到表中可能不适合其他用例。
  • 如果您使用的是 Oracle 数据库 12.2 或更高版本，过滤表移动通常会在简单性权衡中提供最佳性能。 最后一点。 我的测试表只有一个索引：主键。 您的表可能至少有几个其他索引。 这将使进程变慢。 并且可能会改变各自的相对速度。 因此，如果您需要找到删除数据的最快方法，请在您的表上测试每种方法！ 如果您想重复我运行的测试，请使用此 Live SQL 脚本中的remove_rows 过程。 请注意，Live SQL 上的存储配额太小，无法测试删除 100,000 行！ 所以你需要尝试另一个环境。

总结

使用删除删除表中的大部分行是一个缓慢的过程。 擦除的数据越多，速度就越慢。 添加其他用户活动（例如可能会阻止它的更新）以及删除数百万行可能需要几分钟或几小时才能完成。

将流程从 DML 更改为 DDL 可以使流程速度加快几个数量级。 到目前为止，其中最安全的就是[Filtered Table Move](#Delete Data with a Filtered Table Move)。 但是，如果快速 SQL 是您的目标，那么值得尝试上述一些技术，以找到最适合您的过程。 请记住：DDL 在 Oracle 数据库中提交！ 因此，虽然使用 DDL 可以在删除完成时带来巨大的性能提升，但它的风险也更大。 如果在此过程中出现任何问题，您无法回滚语句来恢复丢失的数据。 您必须从备份中恢复。 确保对代码进行双重、三重甚至四重测试，以避免意外从生产数据库中删除大量数据！

实验

  1. 准备测试数据

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    create table bricks ( 
      brick_id integer  
        not null  
        primary key, 
      colour   varchar2(10),  
      shape    varchar2(10), 
      weight   integer 
    )

    create table bricks_child ( 
      brick constraint fk  
        references bricks ( brick_id ) 
        on delete cascade 
    )

    create or replace procedure load_rows ( num_rows integer ) as 
    begin 
      execute immediate 'truncate table bricks cascade'; 
      insert into bricks  
        with rws as ( 
          select level x from dual 
          connect by level <= num_rows 
        ) 
          select rownum, 
                 case mod ( rownum, 3 )  
                   when 0 then 'red' 
                   when 1 then 'blue' 
                   when 2 then 'green' 
                 end, 
                 case mod ( rownum, 2 )  
                   when 0 then 'cube' 
                   when 1 then 'cylinder' 
                 end, 
                 round ( dbms_random.value ( 2, 10 ) ) 
          from   rws; 
    end load_rows; 

    create or replace procedure count_rows as 
      num_rows pls_integer; 
    begin 
      select count (*)  
      into   num_rows 
      from   bricks; 
       
      dbms_output.put_line ( 'Num rows = ' || num_rows ); 
    end count_rows; 

    begin  
      load_rows ( 1000 ); 
      insert into bricks_child values ( 1 ); 
      commit; 
    end;

  2. Truncate Table

    1
    2
    3
    4
    truncate table bricks

    ORA-02266: unique/primary keys in table referenced by enabled foreign keys 
    -- 截断从表中删除所有行。 但是，当启用了引用该表的外键时，您将无法使用它。

  3. Truncate Table Cascade

    1
    2
    3
    truncate table bricks  
      cascade
    -- 添加级联子句还会从子表中删除行。 要使用此功能，您必须将外键声明为 ON DELETE CASCADE。

    查看结果：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    select count (*) from bricks;

    COUNT(*)
    --------
    0

    select count (*) from bricks_child;

    COUNT(*)
    --------
    0

  4. Truncate Table Disable FKs

    您可以在 TRUNCATE 之前禁用外键，而不是 CASCADE 子句。 然后重新启用它们。 您需要先删除子表中的所有行，然后才能执行此操作！

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    declare  
      row_count pls_integer;  
    begin  
      load_rows ( 1000 );  
      count_rows ();  
      execute immediate 'alter table bricks_child   
        modify constraint fk disable';  
      
      execute immediate 'truncate table bricks';  
      
      execute immediate 'alter table bricks_child   
        modify constraint fk enable';  
      count_rows ();  
    end; 

    Statement processed.
    Num rows = 1000
    Num rows = 0

    drop table bricks_child;

  5. Filtered Table Move

    自12.2开始，您可以使用ALTER TABLE ... MOVE的INCLUDING ROWS子句来丢弃与where子句不匹配的行。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    declare  
      row_count pls_integer;  
    begin  
      load_rows ( 1000 );  
      count_rows ();  
      execute immediate 'alter table bricks  
        move including rows  
        where brick_id > 900';  
      count_rows ();  
    end; 

    Statement processed.
    Num rows = 1000
    Num rows = 100

  6. CTAS Switch Rows

    删除大量数据的快速方法是将要保留的行保存到临时表中。 然后截断原始表并将行从临时表复制回。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    declare  
      row_count pls_integer;  
    begin  
      load_rows ( 1000 );  
      count_rows ();  
      execute immediate '  
      create table bricks_keep (  
        brick_id primary key,  
        colour, shape, weight  
      ) as  
        select * from bricks  
        where  brick_id > 900';  
            
      execute immediate 'truncate table bricks';  
        
      execute immediate 'insert into bricks  
        select * from bricks_keep';  
      count_rows ();  
    end; 

    drop table bricks_keep

  7. CTAS Switch Tables

    您可以通过重命名表来切换表，而不是切换行！ 要完成此过程，您还必须将索引、授权等从旧表复制到新表。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    declare  
      row_count pls_integer;  
    begin  
      load_rows ( 1000 );  
      count_rows ();  
      execute immediate '  
      create table bricks_keep (  
        brick_id primary key,  
        colour, shape, weight  
      ) as  
        select * from bricks  
        where  brick_id > 900';  
            
      execute immediate 'rename bricks to bricks_old';  
        
      execute immediate 'rename bricks_keep to bricks';  
      count_rows ();  
    end; 

    Table created.
    Num rows = 1000
    Num rows = 100

    alter table bricks 
      modify partition by range ( brick_id ) 
      interval ( 100 ) ( 
      partition p1 values less than ( 101 ) 
    )

  8. Truncate Partition

    如果表已分区并且您想要删除分区中的所有行，则可以通过截断该分区来实现。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    declare  
      row_count pls_integer;  
    begin  
      load_rows ( 1000 );  
      count_rows ();  
      execute immediate 'alter table bricks  
        truncate partition p1';  
      count_rows ();  
    end; 

    Table truncated.
    Num rows = 1000
    Num rows = 900

  9. Drop Partition

    或者删除整个分区！

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    declare  
      row_count pls_integer;  
    begin  
      load_rows ( 1000 );  
      count_rows ();  
      execute immediate 'alter table bricks  
        drop partition p1';  
      count_rows ();  
    end; 

    Table dropped.
    Num rows = 1000
    Num rows = 900

  10. 创建一个分区表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    create table bricks_part ( 
      brick_id integer  
        not null  
        primary key, 
      colour   varchar2(10),  
      shape    varchar2(10), 
      weight   integer 
    ) partition by range ( brick_id ) ( 
      partition p1 values less than ( 10001 ), 
      partition p2 values less than ( 20001 ), 
      partition p3 values less than ( 30001 ), 
      partition p4 values less than ( 40001 ), 
      partition p5 values less than ( 50001 ), 
      partition p6 values less than ( 60001 ), 
      partition p7 values less than ( 70001 ), 
      partition p8 values less than ( 80001 ), 
      partition p9 values less than ( 90001 ), 
      partition p10 values less than ( 100001 ) 
    )

  11. 计时？

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    create or replace package timing_pkg as  
     
      rows_to_load pls_integer := 100000; 
      start_time pls_integer; 
      time_taken pls_integer; 
       
      procedure set_start_time; 
      procedure calc_runtime (  
        operation varchar2 
      ); 
       
      procedure load_rows; 
      procedure load_rows_partition; 
       
    end;

  12. Test Framework

    测试“删除”方法性能的例程

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    create or replace package body timing_pkg as   
        
      procedure set_start_time as  
      begin  
        start_time := dbms_utility.get_time;  
      end;  
        
      procedure calc_runtime (   
        operation varchar2  
      ) as  
      begin  
        time_taken :=   
          ( dbms_utility.get_time - start_time );  
        dbms_output.put_line ( operation || ' ' || time_taken );  
      end;  
        
      procedure load_rows_partition as  
      begin  
        execute immediate 'truncate table bricks_part';  
        insert into bricks_part  
          with rws as (  
            select level x from dual  
            connect by level <= timing_pkg.rows_to_load  
          )  
            select rownum,  
                   case mod ( rownum, 3 )   
                     when 0 then 'red'  
                     when 1 then 'blue'  
                     when 2 then 'green'  
                   end,  
                   case mod ( rownum, 2 )   
                     when 0 then 'cube'  
                     when 1 then 'cylinder'  
                   end,  
                   round ( dbms_random.value ( 2, 10 ) )  
            from   rws;  
      end;  
        
      procedure load_rows as  
      begin  
        execute immediate 'truncate table bricks';  
        insert into bricks   
          with rws as (  
            select level x from dual  
            connect by level <= timing_pkg.rows_to_load  
          )  
            select rownum,  
                   case mod ( rownum, 3 )   
                     when 0 then 'red'  
                     when 1 then 'blue'  
                     when 2 then 'green'  
                   end,  
                   case mod ( rownum, 2 )   
                     when 0 then 'cube'  
                     when 1 then 'cylinder'  
                   end,  
                   round ( dbms_random.value ( 2, 10 ) )  
            from   rws;  
      end;  
          
    end;

  13. Deleting Rows Benchmark

    要测试不同方法的性能，请运行“EXEC REMOVE_ROWS ( );”，其中 PCT 是要删除的行数中的 0 行和 1 行之间的百分比。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    create or replace procedure remove_rows ( pct number ) is  
    begin  
      begin  
        execute immediate 'drop table bricks_keep purge';  
      exception  
        when others then null;  
      end;  
      begin  
        execute immediate 'drop table bricks_old purge';  
      exception  
        when others then null;  
      end;  
        
      timing_pkg.load_rows;  
      timing_pkg.set_start_time;  
        
      delete bricks  
      where  brick_id <= ( timing_pkg.rows_to_load ) * pct;  
        
      timing_pkg.calc_runtime ( 'Delete-where' );  
      
      timing_pkg.load_rows ();  
      timing_pkg.set_start_time ();  
        
      execute immediate '  
      create table bricks_keep (  
        brick_id primary key,  
        colour, shape, weight  
      ) as  
        select * from bricks  
        where  brick_id > ' ||   
          ( timing_pkg.rows_to_load ) * pct;  
            
      execute immediate 'rename bricks to bricks_old';  
        
      execute immediate 'rename bricks_keep to bricks';  
        
      timing_pkg.calc_runtime ( 'Delete-ctas-switch-table' );  
        
      timing_pkg.load_rows ();  
      timing_pkg.set_start_time ();  
        
      execute immediate '  
      create table bricks_keep (  
        brick_id primary key,  
        colour, shape, weight  
      ) as  
        select * from bricks  
        where  brick_id > ' ||   
          ( timing_pkg.rows_to_load ) * pct;  
            
      execute immediate 'truncate table bricks';  
        
      execute immediate 'insert into bricks  
        select * from bricks_keep';  
        
      timing_pkg.calc_runtime ( 'Delete-ctas-switch-rows' );  
        
      timing_pkg.load_rows ();  
      timing_pkg.set_start_time ();  
        
      execute immediate '  
      alter table bricks  
        move  including rows  
        where brick_id > ' ||   
          ( timing_pkg.rows_to_load ) * pct ||  
        ' online';  
        
      timing_pkg.calc_runtime ( 'Delete-move' );  
        
      timing_pkg.load_rows_partition ();  
      timing_pkg.set_start_time ();  
        
      for i in 1 .. ( pct * 10 ) loop  
        execute immediate 'alter table bricks_part  
          truncate partition p' || i;  
      end loop;  
        
      timing_pkg.calc_runtime ( 'Truncate-partition' );  
        
    end;