教学文章
Technology Exchange
Technology Exchange
热门课程
400电话
免费咨询热线
400-090-9964
在企业的应用场景中，经常会遇到当我们不需要表中的某些行时，需要把它删除。但是有时候并不是想在物理上真正的删除这些数据，在传统的表设计中，我们一般会采用加一个额外的列来表示逻辑删除。比如is_del，当应用程序在处理时，在where条件中根据is_del的值来判断某些行是否应该被删除了。这种解决方案给维护带来了额外的开销，而且缺少灵活性。
Oracle12c版本中增加了一个新特性叫作row archive，可以让数据库自动判断某些行数据是否为删除，这个新功能也叫作In-DatabaseArchiving(数据库内归档)，这个解决方案减少了维护上的开销，而且实现起来非常简单灵活。
简单的说数据库内归档(即行归档)就是在数据库自动创建的隐藏列上给予赋值，如果是0，说明是活跃的数据，可以被查询到，如果是非0的数据，则表示为归档的数据(即被删除的数据)，查询时就不会被查询出来。行归档就像一个开关一样，数据要么是活跃的，要么是归档的，根据用户设置的归档策略数据库自动判断。
本文通过各种案例诠释Oracle 12c中关于ILM(数据生命周期管理)多个新特性中相对最简单的一个――数据库内归档(In-DatabaseArchiving)。
ILM有些特性在12c版本中只有12.2版本才支持，但是行归档功能在12.1版本中就支持，而且支持多租户架构，可以在PDB中使用。
表启用行归档前后的结构变化：
/* 查看启用行归档前表的结构 */
SQL> col COLUMN_NAME for a20
SQL> col DATA_TYPE for a20
SQL> col HIDDEN_COLUMN for a10
SQL> selectCOLUMN_NAME,DATA_TYPE,HIDDEN_COLUMN FROM USER_TAB_COLS WHERE TABLE_NAME='EMP'
COLUMN_NAM DATA_TYPE HIDDEN_COL
---------- -------------------- ----------
EMPNO NUMBER NO
ENAME VARCHAR2 NO
JOB VARCHAR2 NO
MGR NUMBER NO
HIREDATE DATE NO
SAL NUMBER NO
COMM NUMBER NO
DEPTNO NUMBER NO
/* 启用行归档 */
SQL> alter table EMP row archival;
/* 查看启用行归档后表的结构 */
SQL> selectCOLUMN_NAME,DATA_TYPE,HIDDEN_COLUMN FROM USER_TAB_COLS WHERE TABLE_NAME='EMP'
COLUMN_NAME DATA_TYPE HIDDEN_COL
-------------------- ------------------------------
EMPNO NUMBER NO
ENAME VARCHAR2 NO
JOB VARCHAR2 NO
MGR NUMBER NO
HIREDATE DATE NO
SAL NUMBER NO
COMM NUMBER NO
DEPTNO NUMBER NO
SYS_NC00009$ RAW YES
ORA_ARCHIVE_STATE VARCHAR2 YES
可以看出Oracle是使用隐藏列来实现这个功能的，在启用该特性以后，会自动在表上增加SYS_NC00009$和ORA_ARCHIVE_STATE字段，ORA_ARCHIVE_STATE是一个VARCHAR2(4000)的字段。其中SYS_NC00009$是为了以后创建函数索引的时候使用，ORA_ARCHIVE_STATE用来表示行数据的活跃状态。
启用行归档后数据查询操作
查看EMP表中的当前的数据分布：
SQL>SQL> select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp order byhiredate;
ENAME TO_CHAR(HIREDATE,'Y
---------- -------------------
SMITH 1980-12-17 00:00:00
ALLEN 1981-02-20 00:00:00
WARD 1981-02-22 00:00:00
JONES 1981-04-02 00:00:00
BLAKE 1981-05-01 00:00:00
CLARK 1981-06-09 00:00:00
TURNER 1981-09-08 00:00:00
MARTIN 1981-09-28 00:00:00
KING 1981-11-17 00:00:00
JAMES 1981-12-03 00:00:00
FORD 1981-12-03 00:00:00
MILLER 1982-01-23 00:00:00
SCOTT 1987-04-19 00:00:00
ADAMS 1987-05-23 00:00:00
14 rows selected.
将雇佣日期在1981-05-01 00:00:00之前的记录设置为归档。可以通过使用UPDATE语句将ORA_ARCHIVE_STATE字段更新为任意非0的字符来实现。
SQL> update emp set ORA_ARCHIVE_STATE=1
where hiredate < to_date('1981-05-0100:00:00','yyyy-mm-dd hh24:mi:ss');
4 rows updated.
查看修改状态后表的数据：
SQL> selectename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp order by hiredate;
ENAME TO_CHAR(HIREDATE,'Y
---------- -------------------
BLAKE 1981-05-01 00:00:00
CLARK 1981-06-09 00:00:00
TURNER 1981-09-08 00:00:00
MARTIN 1981-09-28 00:00:00
KING 1981-11-17 00:00:00
JAMES 1981-12-03 00:00:00
FORD 1981-12-03 00:00:00
MILLER 1982-01-23 00:00:00
SCOTT 1987-04-19 00:00:00
ADAMS 1987-05-23 00:00:00
10 rows selected.
/* 可以查看这些行的数据活跃状态标识 */
SQL> selectename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),ORA_ARCHIVE_STATE from emporder by hiredate;
ENAME TO_CHAR(HIREDATE,'Y ORA_ARCHIV
---------- ------------------- ----------
BLAKE 1981-05-01 00:00:00 0
CLARK 1981-06-09 00:00:00 0
TURNER 1981-09-08 00:00:00 0
MARTIN 1981-09-28 00:00:00 0
KING 1981-11-17 00:00:00 0
JAMES 1981-12-03 00:00:00 0
FORD 1981-12-03 00:00:00 0
MILLER 1982-01-23 00:00:00 0
SCOTT 1987-04-19 00:00:00 0
ADAMS 1987-05-23 00:00:00 0
10 rows selected.
通过隐藏的列的值，数据库自动判断哪些数据是活跃的，哪些是归档的，活跃的就显示，归档的就不显示，无需添加条件语句。
可以在会话级别控制归档数据的显示情况：
/* 记录是归档的，也显示出来，注意归档的状态标识0为活跃的，非0值为归档的 */
SQL> ALTER SESSION SET ROW ARCHIVALVISIBILITY = ALL;
SQL> select ename,to_char(hiredate,'yyyy-mm-ddhh24:mi:ss'),ORA_ARCHIVE_STATE from emp order by hiredate;
ENAME TO_CHAR(HIREDATE,'Y ORA_ARCHIV
---------- ------------------- ----------
SMITH 1980-12-17 00:00:00 1
ALLEN 1981-02-20 00:00:00 1
WARD 1981-02-22 00:00:00 1
JONES 1981-04-02 00:00:00 1
BLAKE 1981-05-01 00:00:00 0
CLARK 1981-06-09 00:00:00 0
TURNER 1981-09-08 00:00:00 0
MARTIN 1981-09-28 00:00:00 0
KING 1981-11-17 00:00:00 0
JAMES 1981-12-03 00:00:00 0
FORD 1981-12-03 00:00:00 0
MILLER 1982-01-23 00:00:00 0
SCOTT 1987-04-19 00:00:00 0
ADAMS 1987-05-23 00:00:00 0
14 rows selected.
/* 如果不显示归档的数据，则重新设置为默认值：*/
ALTER SESSION SET ROW ARCHIVAL VISIBILITY =ACTIVE;
Session altered.
SQL> select ename,to_char(hiredate,'yyyy-mm-ddhh24:mi:ss'),ORA_ARCHIVE_STATE from emp order by hiredate;
ENAME TO_CHAR(HIREDATE,'Y ORA_ARCHIV
---------- ------------------- ----------
BLAKE 1981-05-01 00:00:00 0
CLARK 1981-06-09 00:00:00 0
TURNER 1981-09-08 00:00:00 0
MARTIN 1981-09-28 00:00:00 0
KING 1981-11-17 00:00:00 0
JAMES 1981-12-03 00:00:00 0
FORD 1981-12-03 00:00:00 0
MILLER 1982-01-23 00:00:00 0
SCOTT 1987-04-19 00:00:00 0
ADAMS 1987-05-23 00:00:00 0
10 rows selected.
数据更新操作
如果会话处于ARCHIVAL VISIBILITY = ACTIVE，如果在UPDATE的时候ORA_ARCHIVE_STATE字段为非0值，则这些行不会被修改;如果需要被修改则要把参数设为ROW ARCHIVAL VISIBILITY = ALL。
SQL> update emp set sal=sal+100 wherehiredate < to_date('1981-12-17 00:00:00','yyyy-mm-dd hh24:mi:ss');
0 rows updated.
如果要能够更新这些行，需要设置如下参数：
SQL> ALTER SESSION SET ROW ARCHIVALVISIBILITY = ALL;
Session altered.
SQL> update emp set sal=sal+100 wherehiredate < to_date('1981-12-17 00:00:00','yyyy-mm-dd hh24:mi:ss');
11 rows updated.
修改成功
总结：被标识为归档的数据，用select查询不出来，那么在进行update和delete操作的时候也一样无法操作。只有在会话级把ROW ARCHIVAL VISIBILITY 设置成all，才可以修改。
那么数据库是如何处理归档的数据呢，虽然我们没有添加条件，但是数据库还是对隐藏字段进行了filter操作。即使是只显示活跃数据，也仍然需要扫描全表。这一点在真实应用中可以通过创建索引来避免全表扫描，也就是数据库内归档虽然没有显示归档的数据，但是归档的数据数据库还是会扫描的。
SQL> select * from emp;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 14 | 560 | 3 (0)| 00:00:01 |
|* 1| TABLE ACCESS FULL| EMP | 14| 560 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1 -filter("EMP"."ORA_ARCHIVE_STATE"='0')
Note
-----
-dynamic statistics used: dynamic sampling (level=2)
/* 如果使用索引扫描，看它的执行计划 */
SQL> select * from emp where empno=7788;
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation |Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 40 | 1 (0)| 00:00:01 |
|* 1| TABLE ACCESS BY INDEX ROWID| EMP | 1 | 40 | 1 (0)| 00:00:01 |
|* 2| INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1 -filter("EMP"."ORA_ARCHIVE_STATE"='0')
2 - access("EMPNO"=7788)
可以看出数据库用了两个条件语句。
数据库内归档可以跟时间有效性管理一起配合使用。我们会在时间有效性的技术文章中给大家说明。
结论：数据库内归档是一个Oracle利用隐藏字段实现的非常简单的功能，但是数据架构人员在规划的时候一定要考虑性能因素，不显示不代表不扫描。
如果EMP表不想再使用归档方式管理行数据，可以禁用这个功能：
/* 查看禁用行归档 */
SQL> alter table EMP no row archival;
/* 查看禁用行归档后表的结构 */
SQL> selectCOLUMN_NAME,DATA_TYPE,HIDDEN_COLUMN FROM USER_TAB_COLS WHERE TABLE_NAME='EMP'
COLUMN_NAME DATA_TYPE HIDDEN_COL
-------------------- ------------------------------
EMPNO NUMBER NO
ENAME VARCHAR2 NO
JOB VARCHAR2 NO
MGR NUMBER NO
HIREDATE DATE NO
SAL NUMBER NO
COMM NUMBER NO
DEPTNO NUMBER NO
可以看出原来在表上增加SYS_NC00009$和ORA_ARCHIVE_STATE字段被自动删除了。
思考题：
Which two statements are true when row archival management is enabled?
A.The ORA_ARCHIVE_STATE column visibility is controlled by the ROW ARCHIVAL VISIBILITY session parameter.
B.The ORA_ARCHIVE_STATE column is updated manually or by a program that could reference activity tracking columns, to indicate that a row is no longer considered active.
C.The ROW ARCHIVAL VISIBILITY session parameter defaults to active rows only.
D.The ORA_ARCHIVE_STATE column is visible if referenced in t he select list of a query.
E.The ORA_ARCHIVE_STATE column is updated automatically by the Oracle Server based on activity tracking columns, to Indicate that a row is no longer considered active.
上一期思考题答案：
Which three tasks can be automatically performed by the Automatic Data Optimization feature of Information lifecycle Management (ILM)?
A. Tracking the most recent read time for a table segment in a user tablespace
B. Tracking the most recent write time for a table segment in a user tablespace
C. Tracking insert time by row for table rows
D. Tracking the most recent write time for a table block
E. Tracking the most recent read time for a table segment in t he SYSTEM tablespace
F. Tracking the most recent write time for a table segment in t he SYSAUX tablespace
Anser：ABD
(解析：启用热图后，内存活动跟踪模块将跟踪所有访问。不跟踪SYSTEM和SYSAUX表空间中的对象)
本篇文章作者介绍：Oracle金牌讲师
北京优技教育科技有限公司
培训机构创始人、Oracle技术首席讲师
自2000年就职于CUUG从事于Oracle的授课和维护工作，经过多年辗转于2006年再度回到CUUG担任Oracle技术讲师，多年的Oracle工作经验，坚持不懈的学习Oracle新的技术，通过举办Oracle技术沙龙(总共累积50多场)，向广大Oracle爱好者传播最新、主流的Oracle技术，为推广Oracle认证做了很多工作，每年培训出众多的Oracle技术人才，为Oracle技术推广和Oracle认证普有着杰出的贡献和突出影响力。
“Oracle WDP项目为个人学习Oracle技术提供了良好的学习途径，对Oracle的普及起到了巨大的作用，很多人通过这个途径学到了Oracle的技术，找到了满意的工作，实现了人生的价值。”