Oracle 12c - Temporal Validity

Temporal Validity 在12C當中還算有趣的新功能,它可以自訂資料的時效性,只讓使用者看到時效內的資料

在Table開啟功能後Table中會自動新增兩個date類型欄位，一個是有效的起始，一個是有效期的結束，欄位可以由User指定也可以由Oracle自動配置，利用這兩個欄位與設定，讓使用者看到在時效內資料，過期的資料就不被看到，就很像是超商店員都會定時檢查飯團，不會讓你買到過期品


--先建立測試用Table 

SQL>  CREATE TABLE test_Redaction
  2      (
  3        t_id     NUMBER,
  4        t_name   VARCHAR2(50),
  5        t_add    VARCHAR2(50),
  6        t_phone  VARCHAR2(50),
  7        t_time   DATE
  8      )
  9      ;

已建立表格.

SQL>

--Insert into 資料

SQL> INSERT INTO TEST_REDACTION VALUES (101,'jimmy','senior program','7458-9658-3256',sysdate);

已建立 1 個資料列.

SQL> INSERT INTO TEST_REDACTION VALUES (102,'mike','general man','1236-6541-3214',sysdate);

已建立 1 個資料列.

SQL> INSERT INTO TEST_REDACTION VALUES (103,'eva','assistant','3574-1596-9856',sysdate);

已建立 1 個資料列.

SQL>

SQL> select * from test_redaction;

      T_ID T_NAME     T_ADD           T_PHONE         T_TIME
---------- ---------- --------------- --------------- -------------------
       101 jimmy      senior program  7458-9658-3256  2014-08-21 02:15:52
       102 mike       general man     1236-6541-3214  2014-08-21 02:17:12 
       103 eva        assistant       3574-1596-9856  2014-08-21 02:17:13

--授權

 GRANT EXECUTE ON dbms_flashback_archive TO test;

--對此session開啟 Temporal Validity

SQL> EXECUTE DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time('CURRENT');

已順利完成 PL/SQL 程序.

--對Table新增功能欄位,這個部份可以手動指定欄位或者Oracle自己新增

自動新增
SQL> alter table test.test_redaction add period for track_time;

已更改表格.

手動指定新增(用此方法指定的欄位並不會被隱藏)
SQL>alter table test.TEST_re3 add period for track_time (t_time1_start,t_time2_end);
已更改表格.

SQL>

--這時候Table 會多3個隱藏欄位

SQL> SELECT COLUMN_NAME,DATA_TYPE,HIDDEN_COLUMN FROM DBA_TAB_COLS WHERE TABLE_NAME='TEST_REDACTION';

COLUMN_NAME          DATA_TYPE                           HID

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

TRACK_TIME           NUMBER                              YES

TRACK_TIME_END       TIMESTAMP(6) WITH TIME ZONE         YES

TRACK_TIME_START     TIMESTAMP(6) WITH TIME ZONE         YES

T_TIME               DATE                                NO

T_PHONE              VARCHAR2                            NO

T_ADD                VARCHAR2                            NO

T_NAME               VARCHAR2                            NO

T_ID                 NUMBER                              NO

已選取 8 個資料列.

SQL>

--設定資料有效的開始與結束

SQL> update test.test_redaction set track_time_start=sysdate ;

已更新 3 個資料列.

--在一分鐘後過期

SQL> update test.test_redaction set track_time_end=sysdate-1/1440 ;

已更新 3 個資料列.

SQL>

在資料過期後,用戶就被遮蔽搜尋不到資料了,不過實際上資料還在

SQL> select * from test_redaction;

沒有任何資料列被選取

SQL>

此功能似乎還不太健全,有時候在開啟功能後過期資料還會被搜尋到, 但過一陣子在搜尋一次過期的資料就搜尋不到了

--我們來看看執行計畫, Oracle 到底做了什麼

SQL> select * from test_redaction;

沒有任何資料列被選取

執行計畫

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

Plan hash value: 3890191689

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

| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time |

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

|   0 | SELECT STATEMENT  |                |     1 |   133 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEST_REDACTION |     1 |   133 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter(("T"."TRACK_TIME_START" IS NULL OR SYS_EXTRACT_UTC("T"."TRACK_TIME_START")<=SYS_EXTRACT_UTC(SYSTIMESTAMP(6))) AND ("T"."TRACK_TIME_END" IS NULL OR SYS_EXTRACT_UTC("T"."TRACK_TIME_END")>SYS_EXTRACT_UTC(SYSTIMESTAMP(6))))

Note

-----

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

統計資料

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

         45  recursive calls

          0  db block gets

         37  consistent gets

          0  physical reads

          0  redo size

        609  bytes sent via SQL*Net to client

        532  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          0  rows processed

SQL>

再執行計劃裡紅字的部份,原來 Oracle 在過程中加入了一個條件過濾

不過這功能玩起來算是新鮮有趣，在某些有時效性的場合，不用設計複雜的程式，直接就可以有此效果