【Oracle Database 12c New Feature】ILM – In-Database

本文介绍Oracle Database 12c中关于数据生命周期管理多个新特性中相对最简单的一个，数据库内归档（In-Database Archiving）。使用的测试表是上一篇介绍数据时间有效期管理中使用的TV表（包括表结构和测试数据），如果你还没有看过上一篇文章，可以先阅读【O

本文介绍Oracle Database 12c中关于数据生命周期管理多个新特性中相对最简单的一个，数据库内归档（In-Database Archiving）。使用的测试表是上一篇介绍数据时间有效期管理中使用的TV表（包括表结构和测试数据），如果你还没有看过上一篇文章，可以先阅读【Oracle Database 12c New Feature】ILM – Temporal Validity。

相比起数据时间有效期管理而言，数据库内归档非常简单，只有一个开关，对于一条数据，要不就是活跃的允许显示，要不就是归档掉不显示，这是由数据库管理员来人工操作的。

在设置数据库内归档之前，必须要在表级别启用该特性。如上一篇文章提到的，In-Database Archiving支持多租户架构，可以在PDB中使用。

SQL> ALTER TABLE TV ROW archival;
 
TABLE altered.

Oracle仍然是使用隐藏列来实现这个功能的，在启用该特性以后，会自动在表上增加ORA_ARCHIVE_STATE字段，这是一个VARCHAR2(4000)的字段。

SQL> SELECT COLUMN_NAME,DATA_TYPE,HIDDEN_COLUMN FROM USER_TAB_COLS WHERE TABLE_NAME='TV';
 
COLUMN_NAME          DATA_TYPE            HID
-------------------- -------------------- ---
ORA_ARCHIVE_STATE    VARCHAR2             YES
SYS_NC00005$         RAW                  YES
VALID_TIME_END       DATE                 YES
VALID_TIME_START     DATE                 YES
INSERT_TIME          DATE                 NO
VALID_TIME           NUMBER               YES
 
6 ROWS selected.

先检查一下TV表中的数据分布，一共有9个不同的时间段，前面5个都只有1条记录，后面4个则有大量测试记录。

SQL> SELECT INSERT_TIME,COUNT(*) FROM TV GROUP BY INSERT_TIME ORDER BY 1;
 
INSERT_TIME         COUNT(*)
----------------- ----------
20130811 09:04:30          1
20130811 09:08:27          1
20130811 09:22:30          1
20130811 09:39:40          1
20130811 09:45:22          1
20130811 09:50:44      19368
20130811 09:50:46      19368
20130811 09:50:47      19368
20130811 09:50:48      19368
 
9 ROWS selected.

尝试将所有20130811 09:50之后的记录全部设置为归档模式。直接使用UPDATE语句将ORA_ARCHIVE_STATE字段更新为任意非0的字符，0表示该记录是活跃的，任何非0字符都表示该记录被归档。

SQL> UPDATE TV SET ORA_ARCHIVE_STATE = '20' WHERE INSERT_TIME>to_date('20130811 09:50','YYYYMMDD HH24:MI');
 
77472 ROWS updated.

再次执行相同的查询语句，可以看到只存在活跃的5条记录了。

SQL> SELECT INSERT_TIME,COUNT(*) FROM TV GROUP BY INSERT_TIME ORDER BY 1;
 
INSERT_TIME         COUNT(*)
----------------- ----------
20130811 09:04:30          1
20130811 09:08:27          1
20130811 09:22:30          1
20130811 09:39:40          1
20130811 09:45:22          1
 
5 ROWS selected.

可以在会话级别设置即使是记录被归档，也仍然显示出来。

SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;
 
SESSION altered.
 
SQL> SELECT INSERT_TIME,COUNT(*) FROM TV GROUP BY INSERT_TIME ORDER BY 1;
 
INSERT_TIME         COUNT(*)
----------------- ----------
20130811 09:04:30          1
20130811 09:08:27          1
20130811 09:22:30          1
20130811 09:39:40          1
20130811 09:45:22          1
20130811 09:50:44      19368
20130811 09:50:46      19368
20130811 09:50:47      19368
20130811 09:50:48      19368
 
9 ROWS selected.

检查ORA_ARCHIVE_STATE值，可以看到所有活跃数据的ORA_ARCHIVE_STATE字段值均为0，这也是在表级别启用数据库内归档以后的默认值。

SQL> SELECT ORA_ARCHIVE_STATE,INSERT_TIME,COUNT(*) FROM TV GROUP BY ORA_ARCHIVE_STATE,INSERT_TIME ORDER BY 2;
 
ORA_ARCHIVE_STATE    INSERT_TIME         COUNT(*)
-------------------- ----------------- ----------
0                    20130811 09:04:30          1
0                    20130811 09:08:27          1
0                    20130811 09:22:30          1
0                    20130811 09:39:40          1
0                    20130811 09:45:22          1
20                   20130811 09:50:44      19368
20                   20130811 09:50:46      19368
20                   20130811 09:50:47      19368
20                   20130811 09:50:48      19368
 
9 ROWS selected.

将其中的一些记录的ORA_ARCHIVE_STATE字段更新为另外的非0字符。

SQL> UPDATE TV SET ORA_ARCHIVE_STATE='ARCHIVING' WHERE INSERT_TIME='20130811 09:50:48';
 
19368 ROWS updated.
 
SQL> SELECT ORA_ARCHIVE_STATE,INSERT_TIME,COUNT(*) FROM TV GROUP BY ORA_ARCHIVE_STATE,INSERT_TIME ORDER BY 2;
 
ORA_ARCHIVE_STATE    INSERT_TIME         COUNT(*)
-------------------- ----------------- ----------
0                    20130811 09:04:30          1
0                    20130811 09:08:27          1
0                    20130811 09:22:30          1
0                    20130811 09:39:40          1
0                    20130811 09:45:22          1
20                   20130811 09:50:44      19368
20                   20130811 09:50:46      19368
20                   20130811 09:50:47      19368
ARCHIVING            20130811 09:50:48      19368

在会话级别重新设置不显示归档数据，可以看到只要是ORA_ARCHIVE_STATE字段不为0的记录都不会显示。

SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;
 
SESSION altered.
 
SQL> SELECT INSERT_TIME,COUNT(*) FROM TV GROUP BY INSERT_TIME ORDER BY 1;
 
INSERT_TIME         COUNT(*)
----------------- ----------
20130811 09:04:30          1
20130811 09:08:27          1
20130811 09:22:30          1
20130811 09:39:40          1
20130811 09:45:22          1

性能考虑，这一点数据库内归档与时间有效性是相同的，都只是对隐藏字段进行了filter操作。即使是只显示活跃数据，也仍然需要扫描全表。这一点在真实应用中可以通过创建索引来避免全表扫描，可以参看MOS Note: Potential SQL Performance Degradation When In Database Row Archiving (Doc ID 1579790.1)，也就是数据库内归档只应该在一个具备良好性能的SQL基础上对返回结果进行过滤，而不要期望归档的记录不参与扫描。

SQL> SELECT * FROM TV;
 
INSERT_TIME
-----------------
20130811 09:04:30
20130811 09:08:27
20130811 09:22:30
20130811 09:39:40
20130811 09:45:22
 
 
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1723968289
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |  8044 |   102   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TV   |     4 |  8044 |   102   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   1 - FILTER("TV"."ORA_ARCHIVE_STATE"='0')
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        375  consistent gets
          0  physical reads
          0  redo SIZE
        648  bytes sent via SQL*Net TO client
        543  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          5  ROWS processed

数据库内归档可以跟时间有效性管理一起配合使用。在会话级别激活时间有效性，可以看到检索不再返回任何数据。执行计划中显示filter条件融合了数据库内归档跟时间有效性两层过滤。

SQL> EXEC dbms_flashback_archive.enable_at_valid_time('CURRENT');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT * FROM tv;
 
no ROWS selected
 
 
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1723968289
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |  6087 |   102   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TV   |     3 |  6087 |   102   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   1 - FILTER("T"."ORA_ARCHIVE_STATE"='0' AND ("T"."VALID_TIME_START"
              IS NULL OR SYS_EXTRACT_UTC(INTERNAL_FUNCTION("T"."VALID_TIME_START"))SYS_EXTRACT_UTC
              (SYSTIMESTAMP(6))))
 
 
Statistics
----------------------------------------------------------
         34  recursive calls
          8  db block gets
        397  consistent gets
          0  physical reads
          0  redo SIZE
        347  bytes sent via SQL*Net TO client
        532  bytes received via SQL*Net FROM client
          1  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          0  ROWS processed

将时间有效期设置为20130811 09:39:50，根据上一篇文章我们设置的1分钟有效期，只有在20130811 09:39:40插入的这条活跃记录可以被显示出来。

SQL> EXEC dbms_flashback_archive.enable_at_valid_time('ASOF',to_date('20130811 09:39:50','YYYYMMDD HH24:MI:SS'));
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT * FROM TV;
 
INSERT_TIME
-----------------
20130811 09:39:40
 
 
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1723968289
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |  6087 |   102   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TV   |     3 |  6087 |   102   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   1 - FILTER("T"."ORA_ARCHIVE_STATE"='0' AND ("T"."VALID_TIME_START"
              IS NULL OR INTERNAL_FUNCTION("T"."VALID_TIME_START")TIMESTAMP' 2013-08-11
              09:39:50.000000000'))
 
 
Statistics
----------------------------------------------------------
         35  recursive calls
          6  db block gets
        398  consistent gets
          0  physical reads
          0  redo SIZE
        550  bytes sent via SQL*Net TO client
        543  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          1  ROWS processed

结论：数据库内归档是一个Oracle利用隐藏字段实现的非常简单的功能，但是数据架构人员在规划的时候一定要考虑性能因素。

Share/Save

Related posts:

1. Oracle 11g new feature – Virtual Column
2. How to Use DBMS_ADVANCED_REWRITE in Oracle 10g
3. 【Oracle Database 12c New Feature】How to Learn Oracle (12c New Feature) from Error