【博客文章2022】同时包含有效时间维度和事务历史时间维度的bi-temporal查询
Author: Bo Tang 1. Oracle表中时间字段的两种维度：
Oracle表中时间字段存在两种维度： a. 有效时间维度：对你的业务应用而言，日期字段本身的有效性或有意义性。
b. 事务历史时间维度：日期字段上或其他字段上曾经提交过的历史值，这些值应该存放在闪回数据归档里。 事务历史时间维度还可以包含如下一些扩展属性：插入该日期数据的日期、做这个改动的人和ip 地址等等。 在flashback as of查询和flashback versions查询中默认都只是显示事务历史时间维度，并且不包含任何扩展属性。利用以下实验回顾一下： 1）准备环境： 2）查看当前会话的上下文：
SQL> select sys_context('userenv','SESSION_USER') from dual;
SYS_CONTEXT('USERENV','SESSION_USER')
--------------------------------------------------------------------------------
SH
SQL> select sys_context('userenv','ip_address') from dual;
SYS_CONTEXT('USERENV','IP_ADDRESS')
--------------------------------------------------------------------------------
192.168.1.85
SQL> select sys_context('userenv','current_user') from dual;
SYS_CONTEXT('USERENV','CURRENT_USER')
--------------------------------------------------------------------------------
SH
SQL> select sys_context('userenv','OS_USER') from dual;
SYS_CONTEXT('USERENV','OS_USER')
--------------------------------------------------------------------------------
oracle
SQL> select sys_context('userenv','authentication_method') from dual;
SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD')
--------------------------------------------------------------------------------
PASSWORD
|
3）查看事务历史时间维度的扩展属性： 首先，由sys进行授权dbms_flashback_archive的执行：
SQL> conn / as sysdba
Connected.
SQL> grant execute on dbms_flashback_archive to sh;
Grant succeeded.
|
sh用户查询不到事务历史时间维度的扩展属性：
SQL>select versions_xid, versions_startscn, versions_operation , a ,
DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT(VERSIONS_XID, 'USERENV','SESSION_USER') su ,
DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT(VERSIONS_XID, 'USERENV','ip_address') ip ,
DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT(VERSIONS_XID, 'USERENV','current_user') cu ,
DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT(VERSIONS_XID, 'USERENV','os_user') ou,
DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT(VERSIONS_XID, 'USERENV','authentication_method') am
from t_timedim
versions between scn minvalue and maxvalue;
-----------------------------------------------------------------------------------------------------------------------------------------------
01001A0009090000 4057367 I 5
06001600030B0000 4057362 I 4
02000F0062090000 4057356 I 3
0A000A00ED0A0000 4057352 I 2
0300100037090000 4057347 I 1
|
然后只需要以sys身份执行一次以下的SET_CONTEXT_LEVEL过程：
SQL>conn / as sysdba
Connected.
SQL>begin
DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL('TYPICAL');
end;
/
PL/SQL procedure successfully completed.
|
附：如果要取消SET_CONTEXT_LEVEL，则执行下面代码：
SQL>conn / as sysdba
Connected.
SQL>begin
DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL('NONE');
end;
/
PL/SQL procedure successfully completed.
|
sh用户查询到了事务历史时间维度的扩展属性： 方法1：
SQL>select versions_xid, versions_startscn, versions_operation , a ,
DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT(VERSIONS_XID, 'USERENV','SESSION_USER') su ,
DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT(VERSIONS_XID, 'USERENV','ip_address') ip ,
DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT(VERSIONS_XID, 'USERENV','current_user') cu ,
DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT(VERSIONS_XID, 'USERENV','os_user') ou,
DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT(VERSIONS_XID, 'USERENV','authentication_method') am
from t_timedim
versions between scn minvalue and maxvalue;
-----------------------------------------------------------------------------------------------------------------------------------------------
08000200040A0000 4074822 U 100 SH 192.168.1.85 SYS oracle
05000A0078090000 4074832 U 200 SH 192.168.1.85 SYS oracle
03000D004A090000 4074838 U 300 SH 192.168.1.85 SYS oracle
0A001E00060B0000 4074856 U 400 SH 192.168.1.85 SYS oracle
06001D000D0B0000 4074862 U 500 SH 192.168.1.85 SYS oracle |
方法2，直接查看flashback data archive：
SQL> select a, startscn , DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT(XID, 'USERENV','SESSION_USER') su ,
DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT(XID, 'USERENV','ip_address') ip ,
DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT(XID, 'USERENV','current_user') cu ,
DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT(XID, 'USERENV','os_user') ou,
DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT(XID, 'USERENV','authentication_method') am
from sys_fba_hist_116155 where startscn is not null;
-----------------------------------------------------------------------------------------------------------------------------------------------
100 4074822 SH 192.168.1.85 SYS oracle
200 4074832 SH 192.168.1.85 SYS oracle
300 4074838 SH 192.168.1.85 SYS oracle
400 4074856 SH 192.168.1.85 SYS oracle
500 4074862 SH 192.168.1.85 SYS oracle |
2. 在有效时间维度上利用period for来实现自动添加where条件（显式方法）：
SQL>CREATE TABLE t_emp
( empid number, salary number, deptid number,
last_name VARCHAR2(20), first_name varchar2(20),
hire_time_start DATE, hire_time_end DATE,
PERIOD FOR hire_time (hire_time_start,hire_time_end));
Table created.
|
3. 在有效时间维度上利用period for来实现自动添加where条件（隐式方法）：
与上面的显式方法相比较，除了不用指定hire_time_start和hire_time_end以外，其他都一样：
SQL> CREATE TABLE t_emp2
( empid number, salary number, deptid number,
last_name VARCHAR2(20), first_name varchar2(20),
PERIOD FOR hire_time);
Table created.
|
下面实验都在t_emp2表上操作，t_emp表的实验方法和结果和t_emp2表的完全一样。 1）准备环境：SQL> insert into t_emp2( empid,salary,deptid, last_name, first_name,hire_time_start,hire_time_end) values (100,10000.00, 90, 'King','Steven', to_date('2010-01-01','YYYY-MM-DD'), to_date('2022-01-01','YYYY-MM-DD'));
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t_emp2( empid,salary,deptid, last_name, first_name,hire_time_start,hire_time_end) values (200,10000.00, 90, 'Whalen','Steven', to_date('2011-01-01','YYYY-MM-DD'), to_date('2011-07-01','YYYY-MM-DD'));
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t_emp2( empid,salary,deptid, last_name, first_name,hire_time_start,hire_time_end) values (300,10000.00, 90, 'Kochhar','Neena', to_date('2012-01-01','YYYY-MM-DD'), to_date('2022-01-01','YYYY-MM-DD'));
1 row created.
SQL>commit;
Commit complete. |
附：如果插入t_emp表，也可以只要像下面这样操作，即插入时不必写字段列表：
SQL>
insert into t_emp values (100,10000.00, 90, 'King','Steven',
to_date('2010-01-01','YYYY-MM-DD'), to_date('2022-01-01','YYYY-MM-DD'));
1 row created.
SQL> commit;
Commit complete.
SQL>
insert into t_emp values (200,10000.00, 90, 'Whalen','Steven',
to_date('2011-01-01','YYYY-MM-DD'), to_date('2011-07-01','YYYY-MM-DD'));
1 row created.
SQL> commit;
Commit complete.
SQL>
insert into t_emp values (300,10000.00, 90, 'Kochhar','Neena',
to_date('2012-01-01','YYYY-MM-DD'), to_date('2022-01-01','YYYY-MM-DD'));
1 row created.
SQL> commit;
Commit complete.
|
表中的数据如图所示： 2）明确写清楚period for的查询： 希望显示2010-01-01存在的员工（有效时间里的数据），相当于select * from t_emp2 where hire_time_start <= to_date('2010-01-01','YYYY-MM-DD') and hire_time_end > to_date('2010-01-01','YYYY-MM-DD')：
图中黄色部分为结果集，代表period for hire_time这个时间点落在表上数据的hire_time_start和hire_time_end之间（以下SQL语句均在SQLDEVELOPER下执行）。
SQL> select * from t_emp2 as of period for hire_time to_date('2010-01-01','YYYY-MM-DD');
---------------------------------------------------------------------------------------
100 10000 90 King Steven
|
希望显示2011-01-01存在的员工（有效时间里的数据），相当于select * from t_emp2 where hire_time_start <= to_date('2011-01-01','YYYY-MM-DD') and hire_time_end > to_date('2011-01-01','YYYY-MM-DD')： 
图中黄色部分为结果集，代表period for hire_time这个时间点落在表上数据的hire_time_start和hire_time_end之间。
SQL> select * from t_emp2 as of period for hire_time to_date('2011-01-01','YYYY-MM-DD');
--------------------------------------------------------------------------------------
100 10000 90 King Steven
200 10000 90 Whalen Steven |
希望显示2012-01-01存在的员工（有效时间里的数据），相当于select * from t_emp2 where hire_time_start <= to_date('2012-01-01','YYYY-MM-DD') and hire_time_end > to_date('2012-01-01','YYYY-MM-DD')： 图中黄色部分为结果集，代表period for hire_time这个时间点落在表上数据的hire_time_start和hire_time_end之间。
SQL> select * from t_emp2 as of period for hire_time to_date('2012-01-01','YYYY-MM-DD');
----------------------------------------------------------------------------------------
100 10000 90 King Steven
300 10000 90 Kochhar Neena
|
希望显示2021-12-31存在的员工（有效时间里的数据），相当于select * from t_emp2 where hire_time_start <= to_date('2021-12-31','YYYY-MM-DD') and hire_time_end > to_date('2021-12-31','YYYY-MM-DD')： SQL> select * from t_emp2 as of period for hire_time to_date('2021-12-31','YYYY-MM-DD');
------------------------------------------------------------------------------------------
100 10000 90 King Steven
300 10000 90 Kochhar Neena |
： 图中黄色部分为结果集，代表period for hire_time这个时间点落在表上数据的hire_time_start和hire_time_end之间。
SQL> select * from t_emp2 as of period for hire_time to_date('2022-01-01','YYYY-MM-DD');
------------------------------------------------------------------------------------------
没有数据
|
注意之所以是“<= 和 >”这样的组合是因为：Oracle强制要求endtime必须大于starttime，不能大于等于。
希望显示从2011-07-01到2022-01-01期间存在的员工（有效时间里的数据），相当于select * from t_emp2 where (hire_time_start <= to_date('2022-01-01','YYYY-MM-DD') and hire_time_end > to_date('2011-07-01','YYYY-MM-DD')) ; 代表period for hire_time between and之间的时间段落在表上数据的hire_time_start和hire_time_end之间。
SQL> select * from t_emp2 versions period for hire_time between to_date('2011-07-01','YYYY-MM-DD') and to_date('2022-01-01','YYYY-MM-DD');
--------------------------------------------------------------------------------------
100 10000 90 King Steven
300 10000 90 Kochhar Neena
|
3）利用dbms_flashback_archive自动添加where条件的查询： 希望显示2010-01-01存在的员工（有效时间里的数据），相当于select * from t_emp2 where hire_time_start <= to_date('2010-01-01','YYYY-MM-DD') and hire_time_end > to_date('2010-01-01','YYYY-MM-DD')：
SQL> begin
dbms_flashback_archive.enable_at_valid_time('ASOF', (to_date('2010-01-01','YYYY-MM-DD')));
end;
/
PL/SQL procedure successfully completed.
SQL> select * from t_emp2;
-------------------------------------------------
100 19999 90 King Steven
|
希望显示2011-01-01存在的员工（有效时间里的数据），相当于select * from t_emp2 where hire_time_start <= to_date('2011-01-01','YYYY-MM-DD') and hire_time_end > to_date('2011-01-01','YYYY-MM-DD')：
SQL> begin
dbms_flashback_archive.enable_at_valid_time('ASOF', (to_date('2011-01-01','YYYY-MM-DD')));
end;
/
PL/SQL procedure successfully completed.
SQL> select * from t_emp2;
-------------------------------------------------
100 10000 90 King Steven
200 10000 90 Whalen Steven |
希望显示2012-01-01存在的员工（有效时间里的数据），相当于select * from t_emp2 where hire_time_start <= to_date('2012-01-01','YYYY-MM-DD') and hire_time_end > to_date('2012-01-01','YYYY-MM-DD')：
SQL> begin
dbms_flashback_archive.enable_at_valid_time('ASOF', (to_date('2012-01-01','YYYY-MM-DD')));
end;
/PL/SQL procedure successfully completed.
SQL> select * from t_emp2;
-------------------------------------------------
100 10000 90 King Steven
300 10000 90 Kochhar Neena
|
希望显示2021-12-31存在的员工（有效时间里的数据），相当于select * from t_emp2 where hire_time_start <= to_date('2021-12-31','YYYY-MM-DD') and hire_time_end > to_date('2021-12-31','YYYY-MM-DD')：
SQL> begin
dbms_flashback_archive.enable_at_valid_time('ASOF', (to_date('2021-12-31','YYYY-MM-DD')));
end;
/
PL/SQL procedure successfully completed.
SQL> select * from t_emp2;
-------------------------------------------------
100 10000 90 King Steven
300 10000 90 Kochhar Neena |
希望显示2022-01-01存在的员工（有效时间里的数据），相当于select * from t_emp2 where hire_time_start <= to_date('2022-01-01','YYYY-MM-DD') and hire_time_end > to_date('2022-01-01','YYYY-MM-DD')：
SQL> begin
dbms_flashback_archive.enable_at_valid_time('ASOF', (to_date('2022-01-01','YYYY-MM-DD')));
end;
/
PL/SQL procedure successfully completed.
SQL> select * from t_emp2;
-------------------------------------------------
没有数据 |
希望显示但今存在的员工（有效时间里的数据），相当于select * from t_emp2 where hire_time_start <= sysdate and hire_time_end > sysdate：
SQL> begin
dbms_flashback_archive.enable_at_valid_time('CURRENT');
end;
/
PL/SQL procedure successfully completed.
SQL> select * from t_emp2;
-------------------------------------------------
没有数据 |
希望取消自动添加的where条件：
SQL> begin
dbms_flashback_archive.enable_at_valid_time('ALL');
end;
/
PL/SQL procedure successfully completed.
SQL> select * from t_emp2;
-------------------------------------------------
100 10000 90 King Steven
200 10000 90 Whalen Steven
300 10000 90 Kochhar Neena |
4. 同时包含有效时间维度和事务历史时间维度的bi-temporal查询：
1）对t_emp2表做一下更新，并提交：
2）明确写清楚period for的bi-temporal 查询：
这样既显示了日期字段本身的有效性或有意义性（希望显示2010-01-01存在的员工），也显示了日期字段上或其他字段上曾经提交过的历史值（10000）：
3）利用dbms_flashback_archive自动添加where条件的bi-temporal 查询： |
