天下彩正版资料大全

400-090-9964


 | 

教学文章

Oracle 12c新特性系列专题讲座-In-Database Archiving(row archive)

时间:2019-05-28 来源:


天下彩正版资料大全   在企业的应用场景中,经常会遇到当我们不需要表中的某些行时,需要把它删除。但是有时候并不是想在物理上真正的删除这些数据,在传统的表设计中,我们一般会采用加一个额外的列来表示逻辑删除。比如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的技术,找到了满意的工作,实现了人生的价值。”