[20131130]12c建表period for valid.txt
9i以后查询过去某个时间点，加入as of timestap 或者 as of scn，但是这个受undo*参数的控制。 当然11G加入Flashback Data
Archive特性，就是通过一个表空间记录表的一些变化,查询历史数据. 实际上这个就是flashback table的扩展(个人认为)!.
12c 有一个新特性 Temporal Validity. 通过加入有效的日期字段，来实现flashback query的特性，可以把它当作flashback query的扩
展。
-- 注意这个特性并不能应用在pdb数据库。
自己测试如下：
1.建立测试环境：
SCOTT@ztest> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SCOTT@ztest> create table t (id number,name varchar2(20),start_date date,end_date date,period for valid(start_date,end_date));
Table created.
--通过period for valid包含两个日期参数，实现这个特性。
2.插入一些测试数据：
insert into t (id,name,start_date,end_date) values ( 1, 'a', trunc(sysdate-5), trunc(sysdate-4) );
insert into t (id,name,start_date,end_date) values ( 2, 'b', trunc(sysdate-4), trunc(sysdate-3) );
insert into t (id,name,start_date,end_date) values ( 3, 'c', trunc(sysdate-3), trunc(sysdate-2) );
insert into t (id,name,start_date,end_date) values ( 4, 'd', trunc(sysdate-2), trunc(sysdate-1) );
insert into t (id,name,start_date,end_date) values ( 5, 'e', trunc(sysdate-1), trunc(sysdate-0) );
commit;
如果插入
SCOTT@ztest> insert into t (id,name,start_date,end_date) values ( 6, 'f', trunc(sysdate+1), trunc(sysdate) );
insert into t (id,name,start_date,end_date) values ( 6, 'f', trunc(sysdate+1), trunc(sysdate) )
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.VALID6B1F6) violated
--因为要求start_date
SCOTT@ztest> SELECT DBMS_METADATA.get_ddl ('TABLE', 'T') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','T')
-----------------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T"
( "ID" NUMBER,
"NAME" VARCHAR2(20),
"START_DATE" DATE,
"END_DATE" DATE,
CONSTRAINT "VALID6B1F6" CHECK ((START_DATE 0)) ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
ILM ENABLE LIFECYCLE MANAGEMENT
SELECT obj#,
col#,
segcol#,
SEGCOLLENGTH,
OFFSET,
NAME,
TYPE#,
LENGTH,
INTCOL#,
default$
FROM sys.col$
WHERE obj# IN (SELECT data_object_id
FROM dba_objects
WHERE owner = USER AND object_name = 'T');
OBJ# COL# SEGCOL# SEGCOLLENGTH OFFSET NAME TYPE# LENGTH INTCOL# DEFAULT$
---------- ---------- ---------- ------------ ---------- -------------------- ---------- ---------- ---------- -----------------------------
92045 0 0 22 0 VALID 2 22 1 438774
92045 1 1 22 0 ID 2 22 2
92045 2 2 20 0 NAME 1 20 3
92045 3 3 7 0 START_DATE 12 7 4
92045 4 4 7 0 END_DATE 12 7 5
--可以发现插入一个valid字段，相当于隐含字段。缺省值438774.
SCOTT@ztest> select t.valid,t.* from t ;
VALID ID NAME START_DATE END_DATE
---------- ---------- -------------------- ------------------- -------------------
438774 1 a 2013-11-27 00:00:00 2013-11-28 00:00:00
438774 2 b 2013-11-28 00:00:00 2013-11-29 00:00:00
438774 3 c 2013-11-29 00:00:00 2013-11-30 00:00:00
438774 4 d 2013-11-30 00:00:00 2013-12-01 00:00:00
438774 5 e 2013-12-01 00:00:00 2013-12-02 00:00:00
3.测试period for valid特性：
SCOTT@ztest> select sysdate-3,t.* from t as of period for valid sysdate-3;
SYSDATE-3 ID NAME START_DATE END_DATE
------------------- ---------- -------------------- ------------------- -------------------
2013-11-29 09:39:44 3 c 2013-11-29 00:00:00 2013-11-30 00:00:00
--仅仅显示1条记录。
SCOTT@ztest> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID b0s6qymaxn4d1, child number 0
-------------------------------------
select t.* from t as of period for valid sysdate-3
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
|* 1 | TABLE ACCESS FULL| T | 1 | 3 (0)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((("T"."START_DATE" IS NULL OR
"T"."START_DATE"
"T"."END_DATE">SYSDATE@!-3)))
--注意过滤条件。(("T"."START_DATE" IS NULL OR "T"."START_DATE"SYSDATE@!-3))
--也就是日期不为空的情况下在start_date与end_date之间的才显示。