12C新特性 时间有效性(temporal validity) [ period for clause ]

12C新特性 时间有效性(temporal validity) [ period for clause ]

设置一条记录的生效时间范围，比如某个产品信息，在某个时间段内是有效的，一般设计表时会在表中增加valid_start和valid_end这两个字段来限制并通过设置这两个字段来实现具体产品的生效范围，如果查看当前生效的记录，则需要通过时间进行过滤。

Oracle 12.1.0.1引入了Temporal Validity（时间有效性），通过这一特性，在不增加列的情况下可以实现时间有效性的的判断

语法：

period for <valid_time_column> ( <start_time_column> , <end_time_column> )
-- 创建表或修改表时指定语法：
-- valid_time_column：用于指定有效时间维度的名称，Oracle将会使用该名称创建数据类型为number的不可见虚拟列；
-- start_time_column：指定起始时间列的名称，数据类型为date或timestamp；
-- end_time_column：指定结束时间列的名称，数据类型为date或timestamp；
-- 如果不指定start_time_column和end_time_column，则会自动创建VALID_START和VALID_END : <valid_time_column>_START和<valid_time_column>_END列。

创建表

使用period for clause
-- 不指定start_time_column和end_time_column
-- 隐式的vaild-time列
create table prod(id number,name varchar2(5),period for valid);
col DATA_TYPE for a30
select column_id,column_name,DATA_TYPE,data_length,NULLABLE,hidden_column,segment_column_id
 from user_tab_cols where table_name='PROD2' order by COLUMN_ID,SEGMENT_COLUMN_ID; 
 COLUMN_ID COLUMN_NAME       DATA_TYPE                      DATA_LENGTH N HID SEGMENT_COLUMN_ID
---------- ----------------- ------------------------------ ----------- - --- -----------------
         1 ID                NUMBER                                  22 Y NO                  3
         2 NAME              VARCHAR2                               100 Y NO                  4
           VALID_START       TIMESTAMP(6) WITH TIME ZONE             13 Y YES                 1
           VALID_END         TIMESTAMP(6) WITH TIME ZONE             13 Y YES                 2
           VALID             NUMBER                                  22 Y YES

-- DESC只能看到2个字段
desc PROD  
Name Null? Type
---- ----- -------------
ID         NUMBER
NAME       VARCHAR2(100)


-- 指定start_time_column和end_time_column
-- 显式指定两个date-time列
create table prod_new(id number,name varchar2(100),valid_start_dt date, valid_end_dt date, period for valid (valid_start_dt,valid_end_dt));

select column_id,column_name,DATA_TYPE,data_length,NULLABLE,hidden_column,segment_column_id
 from user_tab_cols where table_name='PROD_NEW' order by COLUMN_ID,SEGMENT_COLUMN_ID; 
 COLUMN_ID COLUMN_NAME       DATA_TYPE                      DATA_LENGTH N HID SEGMENT_COLUMN_ID
---------- ----------------- ------------------------------ ----------- - --- -----------------
         1 ID                NUMBER                                  22 Y NO                  1
         2 NAME              VARCHAR2                               100 Y NO                  2
         3 VALID_START_DT    DATE                                     7 Y NO                  3
         4 VALID_END_DT      DATE                                     7 Y NO                  4
           VALID             NUMBER                                  22 Y YES

修改现有的表添加时间有效性

alter table test_info7 add period for user_time;

数据测试

insert into prod select rownum id,'Test' from dual connect by rownum<=2;
insert into prod(id,name,VALID_START,VALID_END) select rownum+10 id,'Test',sysdate-(rownum+3)/24/60/60 VALID_START,sysdate-(rownum-3)/24/60/60 VALID_END from dual connect by rownum<=20;
commit;
select * from prod;
select id,name,VALID,VALID_START,VALID_END from prod;
        ID NAME                 VALID VALID_START         VALID_END
---------- --------------- ---------- ------------------- -------------------
         1 Test             430015033
         2 Test             430015033
        11 Test             430015033 2021-08-11 11:09:21 2021-08-11 11:09:27
        12 Test             430015033 2021-08-11 11:09:20 2021-08-11 11:09:26
        13 Test             430015033 2021-08-11 11:09:19 2021-08-11 11:09:25
        14 Test             430015033 2021-08-11 11:09:18 2021-08-11 11:09:24
        15 Test             430015033 2021-08-11 11:09:17 2021-08-11 11:09:23
        16 Test             430015033 2021-08-11 11:09:16 2021-08-11 11:09:22
        17 Test             430015033 2021-08-11 11:09:15 2021-08-11 11:09:21
        18 Test             430015033 2021-08-11 11:09:14 2021-08-11 11:09:20
        19 Test             430015033 2021-08-11 11:09:13 2021-08-11 11:09:19
        20 Test             430015033 2021-08-11 11:09:12 2021-08-11 11:09:18
        21 Test             430015033 2021-08-11 11:09:11 2021-08-11 11:09:17
        22 Test             430015033 2021-08-11 11:09:10 2021-08-11 11:09:16
        23 Test             430015033 2021-08-11 11:09:09 2021-08-11 11:09:15
        24 Test             430015033 2021-08-11 11:09:08 2021-08-11 11:09:14
        25 Test             430015033 2021-08-11 11:09:07 2021-08-11 11:09:13
        26 Test             430015033 2021-08-11 11:09:06 2021-08-11 11:09:12
        27 Test             430015033 2021-08-11 11:09:05 2021-08-11 11:09:11
        28 Test             430015033 2021-08-11 11:09:04 2021-08-11 11:09:10
        29 Test             430015033 2021-08-11 11:09:03 2021-08-11 11:09:09
        30 Test             430015033 2021-08-11 11:09:02 2021-08-11 11:09:08

-- 1 普通查询方式
select * from prod where '2021-08-11 11:09:15' between valid_start and VALID_END;
select id,name,VALID,VALID_START,VALID_END from prod where '2021-08-11 11:09:15' between valid_start and VALID_END;
        ID NAME                 VALID VALID_START         VALID_END
---------- --------------- ---------- ------------------- -------------------
        17 Test             430015033 2021-08-11 11:09:15 2021-08-11 11:09:21
        18 Test             430015033 2021-08-11 11:09:14 2021-08-11 11:09:20
        19 Test             430015033 2021-08-11 11:09:13 2021-08-11 11:09:19
        20 Test             430015033 2021-08-11 11:09:12 2021-08-11 11:09:18
        21 Test             430015033 2021-08-11 11:09:11 2021-08-11 11:09:17
        22 Test             430015033 2021-08-11 11:09:10 2021-08-11 11:09:16
        23 Test             430015033 2021-08-11 11:09:09 2021-08-11 11:09:15


-- 2 SELECT... AS OF查询方式,显示列不能包含VALID字段
select * from prod as of period for valid date'2020-03-01';
select id,name,VALID_START,VALID_END from prod as of period for valid date'2020-03-01';
select id,name,VALID_START,VALID_END from prod as of period for valid '2021-08-11 11:09:15';


-- 3 SELECT... VERSIONS BETWEEN方式,显示列不能包含VALID字段
select * from prod versions period for valid between date '2020-03-01' and date '2020-05-01';
select * from prod versions period for valid between '2021-08-11 11:09:15' and '2021-08-11 11:09:16';
select id,name,VALID_START,VALID_END from prod versions period for valid between '2021-08-11 11:09:15' and '2021-08-11 11:09:16';
        ID NAME            VALID_START         VALID_END
---------- --------------- ------------------- -------------------
         1 Test
         2 Test
        16 Test            2021-08-11 11:09:16 2021-08-11 11:09:22
        17 Test            2021-08-11 11:09:15 2021-08-11 11:09:21
        18 Test            2021-08-11 11:09:14 2021-08-11 11:09:20
        19 Test            2021-08-11 11:09:13 2021-08-11 11:09:19
        20 Test            2021-08-11 11:09:12 2021-08-11 11:09:18
        21 Test            2021-08-11 11:09:11 2021-08-11 11:09:17
        22 Test            2021-08-11 11:09:10 2021-08-11 11:09:16

会话级别的可见性控制

除了使用闪回查询、闪回版本查询数据之外，还可以通过dbms_flashback_archive的存储过程ENABLE_AT_VALID_TIME在会话级别指定表数据的可见性：显示所有数据（默认），显示指定时间的有效数据或者显示当前的有效数据

-- 默认显示所有数据
select * from prod;

-- 显示指定时间的数据
exec dbms_flashback_archive.enable_at_valid_time('ASOF',date '2019-09-01');
exec dbms_flashback_archive.enable_at_valid_time('ASOF','2021-08-11 11:09:15');
select * from prod;

-- 显示当前时间可见的数据
exec dbms_flashback_archive.enable_at_valid_time('CURRENT');
select * from prod;

-- 显示所有数据
exec dbms_flashback_archive.enable_at_valid_time('ALL');
select * from prod;

参考：https://blog.csdn.net/Alen_Liu_SZ/article/details/106290324