12C Temporal Validity （TV）功能探疑

windjack

12C Temporal Validity  （TV）功能探疑

环境：
linux centos 7.8 x86-64

oracle 12.1.0.2 64bit

-----------------------------------------------------------------------------------------------------------------
TV简介：

时间有效期管理（Temporal Validity）以下简称 TV，TV 的功能大致上可以这样描述：

在表中手动或者自动建两个时间类型的字段，一个表示有效期的开始时间，一个表示有效期的结束时间，

就可以通过设置让只有在有效期内的记录才会被选择出来。

--相关实验如下 ：
SQL> conn / as sysdba
Connected.

SQL> create table emp(empno number,salary number,user_start_time date,user_end_time date,period for user_time (user_start_time,user_end_time));
Table created.

SQL> insert into emp values(100,2500,to_date('2020-07-29 22:05:03','yyyy-mm-dd hh24:mi:ss'),to_date('2020-07-29 22:07:03','yyyy-mm-dd hh24:mi:ss'));
1 row created.

SQL> insert into emp values(100,2500,to_date('2020-07-29 22:10:27','yyyy-mm-dd hh24:mi:ss'),to_date('2020-07-29 22:12:27','yyyy-mm-dd hh24:mi:ss'));
1 row created.

SQL> insert into emp values(100,2500,to_date('2020-07-29 22:05:03','yyyy-mm-dd hh24:mi:ss'),to_date('2020-07-29 22:12:27','yyyy-mm-dd hh24:mi:ss'));
1 row created.

SQL> commit;
Commit complete.


SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> col column_name for a30
SQL> select column_name,hidden_column from user_tab_cols where table_name='EMP';

COLUMN_NAME               HIDDEN_COLUMN
------------------------------ ------------------------
USER_TIME                      YES     --自动增加的一个隐藏的用于时间验证的字段
EMPNO                             NO
SALARY                            NO
USER_START_TIME           NO
USER_END_TIME               NO

--未启用时间验证，查询表，有3条数据如下：
SQL> select * from emp;

     EMPNO     SALARY USER_START_TIME     USER_END_TIME
---------- ---------- ------------------- -------------------
       100       2500 2020-07-29 22:05:03 2020-07-29 22:07:03
       200       4700 2020-07-29 22:10:27 2020-07-29 22:12:27
       300       5500 2020-07-29 22:05:03 2020-07-29 22:12:27


--启用会话时间有效性设置：
SQL> exec dbms_flashback_Archive.enable_at_valid_time('ASOF',(to_date('2020-07-29 22:11:01','yyyy-mm-dd hh24:mi:ss')));
PL/SQL procedure successfully completed.

SQL> select * from emp;

     EMPNO     SALARY USER_START_TIME     USER_END_TIME
---------- ---------- ------------------- -------------------
       100       2500 2020-07-29 22:05:03 2020-07-29 22:07:03
       200       4700 2020-07-29 22:10:27 2020-07-29 22:12:27
       300       5500 2020-07-29 22:05:03 2020-07-29 22:12:27

--结果异常，100这条记录不符合要求


SQL> exec dbms_flashback_Archive.enable_at_valid_time('ASOF',(to_date('2020-07-29 22:07:01','yyyy-mm-dd hh24:mi:ss')));

PL/SQL procedure successfully completed.

SQL> select * from emp;

     EMPNO     SALARY USER_START_TIME     USER_END_TIME
---------- ---------- ------------------- -------------------
       100       2500 2020-07-29 22:05:03 2020-07-29 22:07:03
       200       4700 2020-07-29 22:10:27 2020-07-29 22:12:27
       300       5500 2020-07-29 22:05:03 2020-07-29 22:12:27
-- 结果异常，200这条记录不符要求。     


清除共享池：
SQL> alter system flush shared_pool;
System altered.

SQL> select * from emp;

     EMPNO     SALARY USER_START_TIME     USER_END_TIME
---------- ---------- ------------------- -------------------
       100       2500 2020-07-29 22:05:03 2020-07-29 22:07:03
       300       5500 2020-07-29 22:05:03 2020-07-29 22:12:27

结果正确。


--查看执行计划
SQL> set autotrace on
SQL> select * from emp;

     EMPNO     SALARY USER_START_TIME     USER_END_TIME
---------- ---------- ------------------- -------------------
       100       2500 2020-07-29 22:05:03 2020-07-29 22:07:03
       300       5500 2020-07-29 22:05:03 2020-07-29 22:12:27


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    88 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     2 |    88 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("T"."USER_START_TIME" IS NULL OR
              INTERNAL_FUNCTION("T"."USER_START_TIME")<=TIMESTAMP' 2020-07-29
              22:07:01.000000000') AND ("T"."USER_END_TIME" IS NULL OR
              INTERNAL_FUNCTION("T"."USER_END_TIME")>TIMESTAMP' 2020-07-29
              22:07:01.000000000'))
--ORACLE会自动加上filter
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)



--修改时间验证，显示所有记录：
SQL> exec dbms_flashback_Archive.enable_at_valid_time('all');

PL/SQL procedure successfully completed.

SQL> select * from emp;

     EMPNO     SALARY USER_START_TIME     USER_END_TIME
---------- ---------- ------------------- -------------------
       100       2500 2020-07-29 22:05:03 2020-07-29 22:07:03
       300       5500 2020-07-29 22:05:03 2020-07-29 22:12:27
结果不正确。

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    88 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     2 |    88 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("T"."USER_START_TIME" IS NULL OR
              INTERNAL_FUNCTION("T"."USER_START_TIME")<=TIMESTAMP' 2020-07-29
              22:07:01.000000000') AND ("T"."USER_END_TIME" IS NULL OR
              INTERNAL_FUNCTION("T"."USER_END_TIME")>TIMESTAMP' 2020-07-29
              22:07:01.000000000'))
--此处执行计划过滤器异常，ORACLE错误的进行了SQL执行计划重用。
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)




--清除共享池
SQL> alter system flush shared_pool;
System altered.

SQL> select * from emp;

     EMPNO     SALARY USER_START_TIME     USER_END_TIME
---------- ---------- ------------------- -------------------
       100       2500 2020-07-29 22:05:03 2020-07-29 22:07:03
       300       5500 2020-07-29 22:05:03 2020-07-29 22:12:27


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    88 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     2 |    88 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("T"."USER_START_TIME" IS NULL OR
              INTERNAL_FUNCTION("T"."USER_START_TIME")<=TIMESTAMP' 2020-07-29
              22:07:01.000000000') AND ("T"."USER_END_TIME" IS NULL OR
              INTERNAL_FUNCTION("T"."USER_END_TIME")>TIMESTAMP' 2020-07-29
              22:07:01.000000000'))
--执行计划未变
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


--修改时间验证：
SQL> exec dbms_flashback_Archive.enable_at_valid_time('current');
PL/SQL procedure successfully completed.

SQL> select * from emp;

     EMPNO     SALARY USER_START_TIME     USER_END_TIME
---------- ---------- ------------------- -------------------
       100       2500 2020-07-29 22:05:03 2020-07-29 22:07:03
       300       5500 2020-07-29 22:05:03 2020-07-29 22:12:27
--结果不正确

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    88 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     2 |    88 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("T"."USER_START_TIME" IS NULL OR
              INTERNAL_FUNCTION("T"."USER_START_TIME")<=TIMESTAMP' 2020-07-29
              22:07:01.000000000') AND ("T"."USER_END_TIME" IS NULL OR
              INTERNAL_FUNCTION("T"."USER_END_TIME")>TIMESTAMP' 2020-07-29
              22:07:01.000000000'))
--执行计划未变
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

--修改时间验证：
SQL> exec dbms_flashback_Archive.enable_at_valid_time('ASOF',(to_date('2020-07-29 23:07:01','yyyy-mm-dd hh24:mi:ss')));

PL/SQL procedure successfully completed.

SQL> select * from emp;
no rows selected
--结果正常

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    44 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    44 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("T"."USER_START_TIME" IS NULL OR
              INTERNAL_FUNCTION("T"."USER_START_TIME")<=TIMESTAMP' 2020-07-29
              23:07:01.000000000') AND ("T"."USER_END_TIME" IS NULL OR
              INTERNAL_FUNCTION("T"."USER_END_TIME")>TIMESTAMP' 2020-07-29
              23:07:01.000000000'))
--执行计划更新了
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)




--直接使用as of period for user_time来查询

SQL> select * from emp as of period for user_time to_date('2020-07-29 23:07:01','yyyy-mm-dd hh24:mi:ss');
no rows selected
--结果准确

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    44 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    44 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("T"."USER_START_TIME" IS NULL OR
              "T"."USER_START_TIME"<=TO_DATE(' 2020-07-29 23:07:01', 'syyyy-mm-d
d

              hh24:mi:ss')) AND ("T"."USER_END_TIME" IS NULL OR
              "T"."USER_END_TIME">TO_DATE(' 2020-07-29 23:07:01', 'syyyy-mm-dd
              hh24:mi:ss')))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)




--再次使用as of period for user_time来查询
SQL> select * from emp as of period for user_time to_date('2020-07-29 22:07:01','yyyy-mm-dd hh24:mi:ss');

     EMPNO     SALARY USER_START_TIME     USER_END_TIME
---------- ---------- ------------------- -------------------
       100       2500 2020-07-29 22:05:03 2020-07-29 22:07:03
       300       5500 2020-07-29 22:05:03 2020-07-29 22:12:27
--结果准确

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    88 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     2 |    88 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("T"."USER_START_TIME" IS NULL OR
              "T"."USER_START_TIME"<=TO_DATE(' 2020-07-29 22:07:01', 'syyyy-mm-d
d

              hh24:mi:ss')) AND ("T"."USER_END_TIME" IS NULL OR
              "T"."USER_END_TIME">TO_DATE(' 2020-07-29 22:07:01', 'syyyy-mm-dd
              hh24:mi:ss')))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

--------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------

总结：
TV在12.1.0.2版本中使用会话来启用时间有效性验证时非常不稳定，错误的进行cursor执行计划重用，导致结果不准。
all 和current两个参数更不靠谱。
建议大家使用as of period for user_time来进行查询，但网上有人说即使这样用也会出现结果不稳定的现象。
另外，从执行计划中也可以看出来，TV功能使用了filter来处理数据。如果原表有100百条数据，满足要求的只有一条，
ORACLE也会先扫描100万条，再过滤掉999999万条，性能堪忧！


总之，12.1.0.2中 TV功能慎用。