ANBOB™

12.1 New Feature: Valid Time Temporal

前几天录了个好玩的视频,放在了我的weixin公众号上 , 也算是SQL一小魔术, 在没有任何DML的情况下, 只查询数据就发生了改变.

魔术视频地址

Valid Time Temporal 特性就是基于表上的两个时间类型的字段做为开始和结束时间实现数据的有效性逻辑显示控制,oracle 12.1版本引入, 对于表中的记录可以控制只显示在有效时间内或限制时间字段为NULL值时,创建表的语法如下:

CREATE TABLE ... PERIOD FOR <valid_time_column> [(<start_time_column>,<end_time_column>)]

对于valid_time_column列名可以自定义,valid_time_column列是个隐藏列, 而start_time_column和end_time_column列可以指定表上已存在的列也可以不指定,但是如果指定已存在的列要符合end_time_column大于start_time_column的有效性,并且字段类型只能是DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE, 因为在增加该特性时,会在表上增加该约束. 也可以alter table 大已创建的表上启用该特性,如果start_time_column和end_time_column列不指定时,ORACLE 会自动的增加两列,分别为valid_time_column_START 和valid_time_column_END,同样也是隐藏列,在以后drop valid time列时会级连删除, 而之前指定的列不会.

可以使用DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time控制表行记录的显示,是所有还是指定的时间段还是当前时间有效, 当然也可以闪回查询, 行记录的时间可以控制查询也对DML 生效.

了解了以上特性,那我们就可以很方便的模拟之前的那个魔术, 没有任何DML数据发生改变的显像.

drop table magic_table purge;
create table magic_table(message varchar2(100));

alter table magic_table add period for magic;
insert into magic_table values('Hello Wolrd!');
insert into magic_table values('Oops! changed!');
insert into magic_table values('Hi, I am back!');
commit;

select column_name,data_type from user_tab_cols
 where table_name='MAGIC_TABLE' and hidden_column='YES';

EXECUTE DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time('ALL'); 
 
update magic_table set MAGIC_START=systimestamp,MAGIC_END=systimestamp+30/24/60/60 where MESSAGE like 'He%';
update magic_table set MAGIC_START=systimestamp+30/24/60/60,MAGIC_END=systimestamp+50/24/60/60 where MESSAGE like 'O%';
update magic_table set MAGIC_START=systimestamp+50/24/60/60,MAGIC_END=systimestamp+90/24/60/60 where MESSAGE like 'Hi%';

EXECUTE DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time('CURRENT');

select message,MAGIC_START,MAGIC_END,systimestamp from magic_table;

MESSAGE                        MAGIC_START                    MAGIC_END                      SYSTIMESTAMP
------------------------------ ------------------------------ ------------------------------ --------------------------------------
Hello Wolrd!                                                                                 25-NOV-16 01.59.22.978320 PM +08:00
Oops! changed!                                                                               25-NOV-16 01.59.22.978320 PM +08:00
Hi, I am back!                                                                               25-NOV-16 01.59.22.978320 PM +08:00


-- EXECUTE DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time('ALL');

EXECUTE DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time('CURRENT');
update magic_table set message='Hello Wolrd! ';

NOTE:

其实这个魔术就是基于这个新特性,在时间的变化后有效数据发生改变,而影响了数据的显示结果.

打赏

微信扫一扫，打赏作者吧～