重庆思庄Oracle、Redhat认证学习论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

搜索
查看: 651|回复: 0

[原创] 12C Temporal Validity (TV)功能探疑

[复制链接]
发表于 2020-7-29 23:12:31 | 显示全部楼层 |阅读模式
本帖最后由 windjack 于 2020-7-29 23:14 编辑

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功能慎用。








回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛 ( 渝ICP备12004239号-4 )

GMT+8, 2022-1-20 22:25 , Processed in 0.084401 second(s), 21 queries .

重庆思庄学习中心论坛-重庆思庄科技有限公司论坛

© 2001-2020

快速回复 返回顶部 返回列表