Written by: ilmarkerm

Category: Blog entry

Published: August 29, 2014

12c gave us two new features among others: DBMS_UTILITY.EXPAND_SQL_TEXT and temporal validity. DBMS_UTILITY.EXPAND_SQL_TEXT is advertised as a means to expand SQL text that is using views, but it is also very useful to see how Oracle internally rewrites some SQL queries. Here I’m going to use it to see how temporal validity queries are actually executed. All tests are done using 12.1.0.2 Enterprise Edition.

First I’ll create a table for testing with two periods – VALID and ACTIVE. VALID is using DATE datatype and ACTIVE is using hidden TIMESTAMP columns.

CREATE TABLE temporal
(
  id            NUMBER PRIMARY KEY,
  t             VARCHAR2 (100),
  valid_start   DATE,
  valid_end     DATE,
  PERIOD FOR valid (valid_start, valid_end)
);

ALTER TABLE temporal
  ADD period FOR active;


SQL> select column_name, data_type, hidden_column from user_tab_cols where table_name = 'TEMPORAL' order by column_id;

COLUMN_NAME          DATA_TYPE                      HID
-------------------- ------------------------------ ---
ID                   NUMBER                         NO
T                    VARCHAR2                       NO
VALID_START          DATE                           NO
VALID_END            DATE                           NO
ACTIVE               NUMBER                         YES
ACTIVE_START         TIMESTAMP(6) WITH TIME ZONE    YES
ACTIVE_END           TIMESTAMP(6) WITH TIME ZONE    YES
VALID                NUMBER                         YES

8 rows selected.

First syntax that can be used to query temporal data is select id, t from temporal AS OF PERIOD FOR valid TIMESTAMP. Lets use DBMS_UTILITY.EXPAND_SQL_TEXT to look how Oracle actually executes this query (result is formatted for better readability using Toad).

declare
  c clob;
begin
  DBMS_UTILITY.EXPAND_SQL_TEXT(q'[select id, t from temporal as of period for valid DATE'2013-02-01']', c);
  dbms_output.put_line(c);
end;
/

SELECT "A1"."ID" "ID", "A1"."T" "T"
  FROM (SELECT "A2"."ID" "ID",
               "A2"."T" "T",
               "A2"."VALID_START" "VALID_START",
               "A2"."VALID_END" "VALID_END"
          FROM "DBAUSER"."TEMPORAL" "A2"
         WHERE     ("A2"."VALID_START" IS NULL OR "A2"."VALID_START" <= TO_DATE (' 2013-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
               AND ("A2"."VALID_END" IS NULL OR "A2"."VALID_END" > TO_DATE (' 2013-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))) "A1"

No surprises here, Oracle just replaced AS OF PERIOD clause with corresponding WHERE predicates. Another possibility to set the validity period is using DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME. Then select id, t from temporal is turned to:

SQL> exec DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('CURRENT');

declare
  c clob;
begin
  DBMS_UTILITY.EXPAND_SQL_TEXT(q'[select id, t from temporal]', c);
  dbms_output.put_line(c);
end;
/

SELECT "A1"."ID" "ID", "A1"."T" "T"
  FROM (SELECT "A2"."ID" "ID",
               "A2"."T" "T",
               "A2"."VALID_START" "VALID_START",
               "A2"."VALID_END" "VALID_END"
          FROM "DBAUSER"."TEMPORAL" "A2"
         WHERE     ("A2"."VALID_START" IS NULL OR "A2"."VALID_START" <= SYSTIMESTAMP (6))
               AND ("A2"."VALID_END" IS NULL OR "A2"."VALID_END" > SYSTIMESTAMP (6))
               AND ("A2"."ACTIVE_START" IS NULL OR "A2"."ACTIVE_START" <= SYSTIMESTAMP (6))
               AND ("A2"."ACTIVE_END" IS NULL OR "A2"."ACTIVE_END" > SYSTIMESTAMP (6))) "A1"

Two interesting things to note there. First DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME does not currently have a possibility to name the period, so the restriction is applied to ALL periods defined on the table. Another thing, columns valid_start and valid_end were created as DATE, but values are set as TIMESTAMP, making index use impossible (DATE is automatically converted to TIMESTAMP, not the other way around). Not really a problem, just a thing to take a note of, since the default hidden period columns are created as TIMESTAMP, not DATE.