Temporal Validity Support is used with Oracle Flashback Technology for queries that specify the valid time period in AS OF and VERSIONS BETWEEN clauses.
Create a test table and populate with some data as follows:
SQL> CREATE TABLE available_models
2 (
3 recno NUMBER,
4 car_make VARCHAR2(30),
5 car_model VARCHAR2(30),
6 start_time TIMESTAMP,
7 end_time TIMESTAMP,
8 PERIOD FOR model_valid_time
9 (start_time, end_time)
10 );
Table created.
SQL> INSERT INTO available_models
2 VALUES (1, 'Ford', 'Corsair', TO_DATE('1964-01','YYYY-MM'),
3 TO_DATE('1970-01','YYYY-MM'));
1 row created.
SQL> INSERT INTO available_models
2 VALUES (2, 'Ford', 'Country Squire', TO_DATE('1950-01','YYYY-MM'),
3 TO_DATE('1992-01','YYYY-MM'));
1 row created.
SQL> INSERT INTO available_models
2 VALUES (3, 'Ford', 'Executive', TO_DATE('1966-01','YYYY-MM'),
3 TO_DATE('1972-01','YYYY-MM'));
1 row created.
SQL> COMMIT;
Commit complete.The PERIOD FOR clause defines the valid time periods for data. In this example, we are using the START_TIME and END_TIME columns in AVAILABLE_MODELS table.
Table data will have the following distribution:
1950 1964 1966 1970 1972 1992 Ford Corsair |-----------| Ford Country Squire |---------------------------| Ford Executive |--------|
With a period defined on the AVAILABLE_MODELS table, now we can display those car models that were active during a specific date range.
Let's query for a data range where we expect to find all rows returned. Use 1968 as model valid time:
SQL> set linesize 100
SQL> col car_make format a10
SQL> col car_model format a30
SQL> SELECT car_make,
2 car_model,
3 to_char(start_time,'yyyy-mm') STIME,
4 to_char(end_time,'yyyy-mm') ETIME
5 FROM available_models
6 AS OF PERIOD FOR model_valid_time TO_DATE('1968-01','yyyy-mm')
7 ORDER BY 4;
CAR_MAKE CAR_MODEL STIME ETIME
---------- ------------------------------ ------- -------
Ford Corsair 1964-01 1970-01
Ford Executive 1966-01 1972-01
Ford Country Squire 1950-01 1992-01As expected, 3 rows were returned.
Now, just focus on a data subset, for example use 1971 as valid date. We expect to receive only 2 rows as resultset.
SQL> SELECT car_make,
2 car_model,
3 to_char(start_time,'yyyy-mm') STIME,
4 to_char(end_time,'yyyy-mm') ETIME
5 FROM available_models
6 AS OF PERIOD FOR model_valid_time TO_DATE('1971-01','yyyy-mm')
7 ORDER BY 4;
CAR_MAKE CAR_MODEL STIME ETIME
---------- ------------------------------ ------- -------
Ford Executive 1966-01 1972-01
Ford Country Squire 1950-01 1992-01As expected, we received just 2 rows, as "Ford Corsair" element is out of queried range.
Temporal Validity Support can be added to an existing table without explicitly adding columns.
For example, drop and create table AVAILABLE_MODELS without START_TIME and END_TIME columns:
SQL> DROP TABLE available_models PURGE;
Table dropped.
SQL> CREATE TABLE available_models
2 (
3 recno NUMBER,
4 car_make VARCHAR2(30),
5 car_model VARCHAR2(30)
6 );
Table created.Now add the Temporal Validity Support to AVAILABLE_MODELS executing the following ALTER TABLE statement:
SQL> ALTER TABLE available_models
2 ADD PERIOD FOR model_valid_time;
Table altered.The preceding statement adds two hidden columns to the table AVAILABLE_MODELS: "model_valid_time_START" and "model_valid_time_END". As they are hidden columns, these columns do not appear in the output of the SQL*Plus DESCRIBE statement, and SELECT statements show the data in these columns only if the SELECT list explicitly includes the column names.
Let's verify this behavior:
SQL> DESC available_models
Name Null? Type
----------------------------------------- -------- --------------------------
RECNO NUMBER
CAR_MAKE VARCHAR2(30)
CAR_MODEL VARCHAR2(30)
SQL> SELECT recno, car_model,
2 model_valid_time_START,
3 model_valid_time_END
4 FROM available_models;
no rows selectedPopulate the table with the same data, but this time the column list must be defined in the statement:
SQL> INSERT INTO available_models
2 (recno, car_make, car_model,
3 model_valid_time_START, model_valid_time_END)
4 VALUES (1, 'Ford', 'Corsair', TO_DATE('1964-01','YYYY-MM'),
5 TO_DATE('1970-01','YYYY-MM'));
1 row created.
SQL> INSERT INTO available_models
2 (recno, car_make, car_model,
3 model_valid_time_START, model_valid_time_END)
4 VALUES (2, 'Ford', 'Country Squire', TO_DATE('1950-01','YYYY-MM'),
5 TO_DATE('1992-01','YYYY-MM'));
1 row created.
SQL> INSERT INTO available_models
2 (recno, car_make, car_model,
3 model_valid_time_START, model_valid_time_END)
4 VALUES (3, 'Ford', 'Executive', TO_DATE('1966-01','YYYY-MM'),
5 TO_DATE('1972-01','YYYY-MM'));
1 row created.
SQL> COMMIT;
Commit complete.And now execute a sample query to validate range availability. Query table again using 1971 as valid date. We expect to receive only 2 rows as resultset:
SQL> SELECT car_make,
2 car_model,
3 to_char(model_valid_time_END,'yyyy-mm') ETIME
4 FROM available_models
5 AS OF PERIOD FOR model_valid_time TO_DATE('1971-01','yyyy-mm')
6 ORDER BY 3;
CAR_MAKE CAR_MODEL ETIME
---------- ------------------------------ -------
Ford Executive 1972-01
Ford Country Squire 1992-01A future post will address Temporal Validity Support and Flashback technology working together.