Oracle 12c New Feature – Temporal Validity
- Posted by Gavin Soorma
- On August 21, 2013
- 0 Comments
The Temporal Validity feature adds a time dimension to each row in the table consisting of two date-time columns to denote validity of data. Data that is older or no longer valid or not yet valid can be hidden from queries and only active data returned by queries.
In case of a very large table, just processing a small active row set rather than the entire table can be a significant performance gain.
The Temporal Validity is controlled by the user or application who defines the valid time dimension for the table via the PERIOD FOR caluse
Until now we had data and timestamp columns in table – but they record the transaction time. For example a table called EMP has a column called DOJ and this records the transaction time of when the entry was made in the EMP table for a particular employee.
But suppose we have a case where contracts are offered to potential employees on a particular date and we want to run queries based on this timestamp and not when the DOJ record for that employee is entered in the database
Let us look at an example.
We create a table MYSALES and alter the table adding the valid-time dimension TRACK_TIME via the PERIOD FOR clause as shown below
SQL> create table sh.mysales as select * from sh.sales; Table created. SQL> select count(*) from sh.mysales; COUNT(*) ---------- 918843 SQL> alter table sh.mysales add period for track_time; Table altered.
Oracle will create hidden columns TRACK_TIME, TRACK_TIME_START and TRACK_TIME_END
SQL> desc mysales Name Null? Type ----------------------------------------- -------- ---------------------------- PROD_ID NOT NULL NUMBER CUST_ID NOT NULL NUMBER TIME_ID NOT NULL DATE CHANNEL_ID NOT NULL NUMBER PROMO_ID NOT NULL NUMBER QUANTITY_SOLD NOT NULL NUMBER(10,2) AMOUNT_SOLD NOT NULL NUMBER(10,2) SQL> select column_name,data_type from user_tab_cols where table_name='MYSALES' and hidden_column='YES'; COLUMN_NAME DATA_TYPE -------------------------------------------------- ---------------------------------------- TRACK_TIME NUMBER TRACK_TIME_END TIMESTAMP(6) WITH TIME ZONE TRACK_TIME_START TIMESTAMP(6) WITH TIME ZONE
We can filter on valid-time columns using the SELECT statement with the PERIOD FOR clause or use the DBMS_FLASHBACK_ARCHIVE procedure.
The DBMS_FLASHBACK_ARCHIVE controls the visibility of the data as at a given time or data which is currently valid within the valid time period at the session level or can also set the visibility to the full table level.
This visibility control applies to not only SELECT statements but also to DML statements
SQL> select count(*) from sh.sales; COUNT(*) ---------- 918843
Update the table so that only row in the table with timestamp greater than 01-JAN-2000 are considered to be valid or active.
SQL> update sh.mysales set track_time_start=sysdate ; 918843 rows updated. SQL> update sh.mysales set track_time_end=sysdate where time_id < '01-JAN-2000'; 426779 rows updated. SQL> EXECUTE DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time('CURRENT'); PL/SQL procedure successfully completed.
So now when we do a count(*) from the table MYSALES, it only returns the rows which we have deemed to be active or current and not all the rows.
SQL> select count(*) from sh.mysales; COUNT(*) ---------- 492064
Let us now increase the period for the active or valid rows from 01-JAN-2001 to 01-JAN-2001
SQL> update sh.mysales set track_time_end=sysdate where time_id < '01-JAN-2001'; 659425 rows updated.
Now when we issue the same SELECT statement, because fewer rows are considered active, the query now returns 259418 rows as opposed to the earlier 492064 rows
SQL> EXECUTE DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time('CURRENT'); PL/SQL procedure successfully completed. SQL> select count(*) from sh.mysales; COUNT(*) ---------- 259418 SQL> EXECUTE DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time('ALL'); PL/SQL procedure successfully completed. SQL> select count(*) from sh.mysales; COUNT(*) ---------- 918843
0 Comments