ITPub博客

首页 > 数据库 > Oracle > [20131130]12c建表period for valid.txt

[20131130]12c建表period for valid.txt

原创 Oracle 作者:lfree 时间:2013-12-02 10:19:53 0 删除 编辑
[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< end_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 < END_DATE) and (VALID > 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"<=SYSDATE@!-3) AND ("T"."END_DATE" IS NULL OR
              "T"."END_DATE">SYSDATE@!-3)))


--注意过滤条件。(("T"."START_DATE" IS NULL OR "T"."START_DATE"<=SYSDATE@!-3) AND ("T"."END_DATE" IS NULL OR "T"."END_DATE">SYSDATE@!-3))
--也就是日期不为空的情况下在start_date与end_date之间的才显示。




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1061815/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2669
  • 访问量
    6427260