Oracle 数据库行归档演示

在最近的一个客户项目中，我从 LOG 表中删除了数百万行。删除 3 个月的数据需要 30 多分钟才能进行最佳优化。

过了一会儿，我想起了自 Oracle 12cR1 以来存在的一个非常好的 Oracle 特性使我们能够自动归档行。

接下来，让我解释一下它是如何工作的：

我有一个有 1000 万行的大表：

SQL> create table bigtable
segment creation immediate
nologging
as
with generator as (
        select
                rownum id
        from dual
        connect by
                level <= 10000000
)
select
        rownum                          id,
        mod(rownum-1,3)                 val1,
        mod(rownum-1,10)                val2,
        lpad('x',100,'x')               padding
from
        generator       v1
order by
        dbms_random.value
 21  ;

Table created.

SQL> exec dbms_stats.gather_table_stats('SYS','BIGTABLE');

PL/SQL procedure successfully completed.

SQL> desc bigtable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 VAL1                                               NUMBER
 VAL2                                               NUMBER
 PADDING                                            VARCHAR2(100)

SQL> select count(*) from bigtable;

  COUNT(*)
----------
  10000000

SQL>

让我们看看与 VAL2 列相关的数据分散：

SQL> select count(*),val2 from bigtable group by val2;

  COUNT(*)       VAL2
---------- ----------
   1000000          6
   1000000          1
   1000000          7
   1000000          8
   1000000          2
   1000000          4
   1000000          5
   1000000          9
   1000000          3
   1000000          0

10 rows selected.

SQL>

删除与 VAL2 value = 8 相关的行：

SQL> set timing on
SQL> delete from bigtable where val2=8;
SQL > commit;

1000000 rows deleted.

Elapsed: 00:00:02.97
SQL> explain plan for delete from bigtable where val2=8;

Explained.

Elapsed: 00:00:00.00
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1385633249

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |          |  1000K|  2929K| 44506   (1)| 00:00:02 |
|   1 |  DELETE            | BIGTABLE |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| BIGTABLE |  1000K|  2929K| 44506   (1)| 00:00:02 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter("VAL2"=8)

14 rows selected.

Elapsed: 00:00:00.02
SQL>

由于 VAL2 列上没有索引，因此这些行已被删除，并且 oracle 优化器执行完全扫描以访问数据。DELETE sql 语句执行大约需要 3 秒。

出于任何原因如果你想恢复这个“删除”行，只有从备份中“闪回”或“恢复”才能帮助你。

现在让我们在 BIGTABLE 表上启用 ROW ARCHIVAL：

SQL> alter table bigtable row archival;

Table altered.

Elapsed: 00:00:00.05
SQL>

创建了一个名为_ora_archive_state_的新隐藏列，指示行是否已归档。该列仅通过 SELECT 命令显示，而不是在 DESC 命令中显示。

SQL> desc bigtable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 VAL1                                               NUMBER
 VAL2                                               NUMBER
 PADDING                                            VARCHAR2(100)

SQL> select ID,VAL1,VAL2,PADDING,ora_archive_state from bigtable where rownum = 1;

ID       VAL1       VAL2  PADDING       ORA_ARCHIVE_STATE
--------------------------------------------------------------------------------
4091832  2          1     xxxxxxxxxxx   0


Elapsed: 00:00:00.00
SQL>

_ora_archive_state_中的值 0表示该行未归档，因此可通过 SELECT 看到。

确认所有行都处于活动状态（未归档）：

SQL> select count(*) from bigtable;

  COUNT(*)
----------
  10000000

现在假设我们要归档所有值为 VAL2=6 的行。

修改_ora_archive_state_的值：

SQL> update bigtable set ora_archive_state=dbms_ilm.archivestatename(1) where val2=6;

1000000 rows updated.

Elapsed: 00:00:37.94
SQL> commit;

Commit complete.

Elapsed: 00:00:01.28
SQL>

计算现在活动的行数：

SQL> select count(*) from bigtable;

  COUNT(*)
----------
   8000000

Elapsed: 00:00:02.17
SQL>

归档的行不再可见，它们已被“逻辑”删除（或归档）：

如果我们想查看是否归档的所有行，请将会话的归档可见性设置为 ALL：

SQL> alter session set row archival visibility = all;

Session altered.

Elapsed: 00:00:00.00

SQL> select count(*) from bigtable;

  COUNT(*)
----------
  90000000

SQL> select count(*),val2,ora_archive_state from bigtable group by val2, ora_archive_state;

  COUNT(*)       VAL2         ORA_ARCHIVE_STATE
--------------------------------------------------------------------------------
1000000          1           0
1000000          4           0
1000000          6           1
1000000          3           0
1000000          5           0
1000000          9           0
1000000          7           0
1000000          2           0
1000000          0           0

9 rows selected.

要仅查看未归档的行，请将归档可见性设置为 ACTIVE：

SQL> alter session set row archival visibility = active;

Session altered.

Elapsed: 00:00:00.00
SQL> select count(*),val2,ora_archive_state from bigtable group by val2, ora_archive_state;

  COUNT(*)       VAL2 O
---------- ---------- -
   1000000          1 0
   1000000          7 0
   1000000          2 0
   1000000          4 0
   1000000          5 0
   1000000          9 0
   1000000          3 0
   1000000          0 0

8 rows selected.

结论

Oracle Database Row Archiving 使您能够“逻辑地”归档和删除行，一般可以用来归档大表中的行。

这一功能的实现非常简单，可以在必须处理大表中的存档/删除行时为您提供帮助。

原文标题：Oracle Database Row Archiving
原文作者：Lazhar Felahi
原文地址：https://www.dbi-services.com/blog/oracle-database-row-archiving/