This post is part 4 of the Write Less with More series of blog posts, focusing on new features of Oracle 12c that allow us developers to write less than in previous versions in order to achieve the same functionality. Each part is dedicated for one new feature that solves some common development task. For more details, including the setup of the examples used throughout the series and the list of development tasks that drive the series, please see part 1.

All the Series Parts

Published

Part 1 – write less configuration with SQL*Loader Express Mode
Part 2 – write less application code with Identity Columns
Part 3 – write less application code with In-Database Archiving
Part 4 – write less application code with Temporal Validity
Part 5 – write less code in SQL with Row Limiting
Part 6 – write less “inappropriately located” code with SELECT FROM Package-Level Collection Types
Part 7 – write less code in SQL with Lateral Inline Views
Part 8 – write less “inappropriately located” code with PL/SQL in the WITH Clause

Task #4

PROJECT_ASSIGNMENTS is too simplistic. At this point (after it was defined in part 1 and enhanced in Part 2) it only says which people are assigned to which projects, not when or for how long. Now we want to add this missing information.

A Pre-12c Solution

Before 12c we could add a pair of date/time columns to represent the active period boundaries, e.g. PERIOD_START and PERIOD_END:

alter table project_assignments add (
  period_start date,
  period_end   date
);

When dealing with time periods there are three important decisions to make:
One decision is how to represent periods that their start point or end point (or even both) are unlimited. I will use NULL for this.

I recommend reading Stew Ashton’s post about his preference of not using NULL to represent unlimited period boundaries. It doesn’t matter if you agree or not – reading about the reasons is the important part. Actually, I recommend reading everything Stew writes 🙂

Another decision is whether the period boundaries are inclusive or exclusive. I like to include the start point in the period and exclude the end point. In this way, if we want to assign someone to a project for the whole month of February 2015 (for example) we will use quite simple values:

insert into project_assignments (person_id,project_id,period_start,period_end)
values (101,1,date'2015-02-01',date'2015-03-01');

If I’d choose to include both the start point and the end point in the period, I would have to use a more complex value:

insert into project_assignments (person_id,project_id,period_start,period_end)
values (101,1,date'2015-02-01',timestamp'2015-02-28 23:59:59');

And a third decision is which data type to use for the start/end columns – DATE, TIMESTAMP or TIMESTAMP WITH TIME ZONE. This decision is derived from the functional requirements for period boundary granularity (and your data modeling standards, if you have any [I hope you do]).

After we added the columns to the table, we need to add conditions to all the relevant SQL statements in the application (or applications).
For example, if we want to get all the assignments that are active on a specific date:

select * from project_assignments a
where (a.period_start is null or a.period_start <= :my_date)
  and (a.period_end is null or a.period_end > :my_date);

And if we want to get all the assignments that are active sometime within a specific time range:

select * from project_assignments a
where (a.period_start is null or a.period_start < :my_end_date)
  and (a.period_end is null or a.period_end > :my_start_date);

A 12c Solution

Temporal Validity

In Oracle 12c a table can be defined with the PERIOD FOR clause, to associate it with one or more valid time dimensions. Each such dimension consists of a pair of date/time columns. These columns can be created either explicitly or implicitly. As a result, the data can become visible or invisible, based on statement- or session-level definitions.
In our case we can simply add a period called ASSIGNMENT_PERIOD to the table:
 

alter table project_assignments 
       add PERIOD FOR assignment_period;

This statement added two hidden TIMESTAMP WITH TIME ZONE columns – ASSIGNMENT_PERIOD_START and ASSIGNMENT_PERIOD_END – which can (and should) be set explicitly when records of PROJECT_ASSIGNMENTS are inserted and updated.
When we DESCRIBE the table, we see only the columns we explicitly defined:

> desc project_assignments
 Name                                         Null?    Type
 -------------------------------------------- -------- ----------------------
 ASSIGNMENT_ID                                NOT NULL NUMBER(38)
 PERSON_ID                                    NOT NULL NUMBER(38)
 PROJECT_ID                                   NOT NULL NUMBER(38)

But if we select from USER_TAB_COLS we discover the new columns – ASSIGNMENT_PERIOD_START and ASSIGNMENT_PERIOD_END (and one virtual column – ASSIGNMENT_PERIOD – that I will not discuss here):

> select column_name,data_type,nullable,hidden_column,virtual_column,user_generated
    from user_tab_cols
    where table_name = 'PROJECT_ASSIGNMENTS';

COLUMN_NAME                    DATA_TYPE                   NUL HIDDEN_CO VIRTUAL_C USER_GENE
------------------------------ --------------------------- --- --------- --------- ---------
ASSIGNMENT_ID                  NUMBER                      N   NO        NO        YES
PERSON_ID                      NUMBER                      N   NO        NO        YES
PROJECT_ID                     NUMBER                      N   NO        NO        YES
ASSIGNMENT_PERIOD_START        TIMESTAMP(6) WITH TIME ZONE Y   YES       NO        NO
ASSIGNMENT_PERIOD_END          TIMESTAMP(6) WITH TIME ZONE Y   YES       NO        NO
ASSIGNMENT_PERIOD              NUMBER                      Y   YES       YES       NO

Note: there is a major difference between the data dictionary views USER_TAB_COLUMNS and USER_TAB_COLS: while the former exposes only the explicitly defined columns, the latter includes also system-generated columns.

Update [17-Sep-2021] Connor McDonald shows how the period information is stored in the data dictionary – https://connor-mcdonald.com/2021/09/17/is-my-table-temporal/

In addition to the columns, Oracle also implicitly created a check constraint, to make sure the start time is always before the end time:

> select constraint_name,constraint_type,search_condition
  from user_constraints
  where table_name='PROJECT_ASSIGNMENTS'
    and constraint_name like 'ASSIGNMENT_PERIOD%';

CONSTRAINT_NAME                CON SEARCH_CONDITION
------------------------------ --- ------------------------------------------------------
ASSIGNMENT_PERIOD3366E0        C   ASSIGNMENT_PERIOD_START < ASSIGNMENT_PERIOD_END

Note that the condition is “less than” and not “less than or equal to”. The Temporal Validity feature uses the same rule I used in the pre-12c solution – the period includes the start time and excludes the end time.

Now, let’s fill the table with some data. We need to explicitly set values to the system-generated columns:

insert into project_assignments
    (person_id,
     project_id,
     assignment_period_start,
     assignment_period_end)
values
    (101,
     1,
     date'2015-09-16',
     date'2015-09-18');

I added some more records as a preparation for the next examples. This is the content of the table at this point:

> select person_id,
         project_id,
         assignment_period_start,
         assignment_period_end
  from   project_assignments
  order  by assignment_period_start,
            assignment_period_end;

                      PERIOD     PERIOD
 PERSON_ID PROJECT_ID START      END
---------- ---------- ---------- ----------
       101          1 16/09/2015 18/09/2015
       102          1 20/09/2015 22/09/2015
       101          1 22/09/2015 24/09/2015
       101          3 26/09/2015 27/09/2015
       101          2 27/09/2015 30/09/2015
       102          2 27/09/2015 30/09/2015
       101          1 28/09/2015 30/09/2015
       104          2 28/09/2015 30/09/2015
       103          2 28/09/2015 30/09/2015
       102          1 28/09/2015 30/09/2015
       201          2 29/09/2015 30/09/2015
       102          3 29/09/2015
       101          1 04/10/2015 06/10/2015
       102          1 06/10/2015 08/10/2015
       101          1 10/10/2015 12/10/2015
       102          1 14/10/2015 16/10/2015
       102          1 22/10/2015 24/10/2015
       103          1            03/10/2015

18 rows selected.

Nulls represent unlimited start time or end time.

Statement-Level Control

To get, in a specific statement, only records that are active on a specific date, we add the AS OF PERIOD clause.
For example, to get only the project assignments that are active today (when today is September 29th, 2015):

> select person_id,
         project_id,
         assignment_period_start,
         assignment_period_end
  from   project_assignments
  AS OF PERIOD FOR assignment_period SYSDATE;

                      PERIOD     PERIOD
 PERSON_ID PROJECT_ID START      END
---------- ---------- ---------- ----------
       101          1 28/09/2015 30/09/2015
       102          1 28/09/2015 30/09/2015
       103          1            03/10/2015
       101          2 27/09/2015 30/09/2015
       102          2 27/09/2015 30/09/2015
       103          2 28/09/2015 30/09/2015
       104          2 28/09/2015 30/09/2015
       201          2 29/09/2015 30/09/2015
       102          3 29/09/2015

9 rows selected.

Or to get only the project assignments that will be active on October 10th, 2015:

> select person_id,
         project_id,
         assignment_period_start,
         assignment_period_end
  from   project_assignments
  AS OF PERIOD FOR assignment_period date'2015-10-10';

                      PERIOD     PERIOD
 PERSON_ID PROJECT_ID START      END
---------- ---------- ---------- ----------
       101          1 10/10/2015 12/10/2015
       102          3 29/09/2015

If we want to get only records that are active in a specific time range, we use the VERSIONS PERIOD clause. For example:

> select person_id,
         project_id,
         assignment_period_start,
         assignment_period_end
  from   project_assignments
  VERSIONS PERIOD FOR assignment_period
    between date'2015-09-21' and date'2015-09-23';

                      PERIOD     PERIOD
 PERSON_ID PROJECT_ID START      END
---------- ---------- ---------- ----------
       101          1 22/09/2015 24/09/2015
       102          1 20/09/2015 22/09/2015
       103          1            03/10/2015

Session-Level Control

We can also filter non-active records out in the session-level, affecting all the statements without changing them. We do it by calling the procedure DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME. This procedure has 3 options.
The first option it to pass the value ‘CURRENT’ to the LEVEL parameter (the first parameter), which means that all the subsequent statements in the session will consider only records that are active (or valid) at the execution time, for all the tables that were defined with the PERIOD FOR clause.

> exec dbms_flashback_archive.enable_at_valid_time('CURRENT')

PL/SQL procedure successfully completed.

> select person_id,
         project_id,
         assignment_period_start,
         assignment_period_end
  from   project_assignments;

                      PERIOD     PERIOD
 PERSON_ID PROJECT_ID START      END
---------- ---------- ---------- ----------
       101          1 28/09/2015 30/09/2015
       102          1 28/09/2015 30/09/2015
       103          1            03/10/2015
       101          2 27/09/2015 30/09/2015
       102          2 27/09/2015 30/09/2015
       103          2 28/09/2015 30/09/2015
       104          2 28/09/2015 30/09/2015
       201          2 29/09/2015 30/09/2015
       102          3 29/09/2015

9 rows selected.

The second option is to pass the value ‘ASOF’ (read “as of”) to the LEVEL parameter. In this case we need to pass also a concrete timestamp value to the QUERY_TIME parameter (the second parameter).

> exec dbms_flashback_archive.enable_at_valid_time('ASOF',date'2015-10-10')

PL/SQL procedure successfully completed.

> select person_id,
         project_id,
         assignment_period_start,
         assignment_period_end
  from   project_assignments;

                      PERIOD     PERIOD
 PERSON_ID PROJECT_ID START      END
---------- ---------- ---------- ----------
       101          1 10/10/2015 12/10/2015
       102          3 29/09/2015

And the third option is to pass the value ‘ALL’ to the LEVEL parameter, which means no filtering occurs in subsequent statements.

> exec dbms_flashback_archive.enable_at_valid_time('ALL')

PL/SQL procedure successfully completed.

> select person_id,
         project_id,
         assignment_period_start,
         assignment_period_end
  from   project_assignments;

                      PERIOD     PERIOD
 PERSON_ID PROJECT_ID START      END
---------- ---------- ---------- ----------
       101          1 16/09/2015 18/09/2015
       101          1 22/09/2015 24/09/2015
       101          1 28/09/2015 30/09/2015
       101          1 04/10/2015 06/10/2015
       101          1 10/10/2015 12/10/2015
       102          1 20/09/2015 22/09/2015
       102          1 28/09/2015 30/09/2015
       102          1 06/10/2015 08/10/2015
       102          1 14/10/2015 16/10/2015
       102          1 22/10/2015 24/10/2015
       103          1            03/10/2015
       101          2 27/09/2015 30/09/2015
       102          2 27/09/2015 30/09/2015
       103          2 28/09/2015 30/09/2015
       104          2 28/09/2015 30/09/2015
       201          2 29/09/2015 30/09/2015
       101          3 26/09/2015 27/09/2015
       102          3 29/09/2015

18 rows selected.

Warning:
if the value you pass to the LEVEL parameter of dbms_flashback_archive.enable_at_valid_time is other than ‘CURRENT’, ‘ASOF’ or ‘ALL’, it will NOT change anything and the previous setting will still take effect.
The dangerous part is that the procedure does not throw any exception; it completes successfully (doing nothing).
And these 3 values are case-sensitive. Passing ‘current’ or ‘AsOf’, for example, will silently do nothing.

There are two sides to every coin

We’ve just seen that Temporal Validity adds hidden columns to tables and hidden predicates to SQL statements. This is cool, but also dangerous. When things are hidden, it is easy to forget them, but we shouldn’t. See what I’ve already written about that with regards to In-Database Archiving in part 3.

Conclusion

We saw in this part of the Write Less with More series that Temporal Validity allows us to write less application code.

The next post will be about another new Oracle 12c feature – Row Limiting.