How to Use the Period Definition in Oracle 12c

One of the new features in Oracle 12c is a concept called "period definition" or "temporal validity."

It's a useful feature that makes it easier for you to write queries in certain situations. In this article, I'll explain what this feature is and how you can use it.

What Is This "Period Definition" Feature?

Have you ever seen a database table with records that used effective dates, such as a start date and end date, to indicate if a record was valid?

I've seen this in most databases I've worked on, and I've also created a few that used this feature.

Basically, a table has a start date field and an end date field. These would both be date types.

The fields indicate a date range that this record is valid for.

You would then use these date fields in your queries to work out which record is valid, or the record that has a valid date range.

In Oracle 12c, the feature of "period definition", or "temporal validity" has been introduced.

It makes it easier for Oracle, and you, to track which records are valid on a date.

So, how can you use this feature?

How To Add Period Definition to a Table

First, you need to add a period definition to a table.

Let's say you had a table like this:

CREATE TABLE product (
  product_Id NUMBER,
  product_name VARCHAR2(100),
  price NUMBER(10, 2),
  start_date DATE,
  end_date DATE);

Normally, you would have these two date fields, and you query would need to have a WHERE clause that checked them individually, which makes it more complicated and may reduce performance.

To add this period definition feature to this table, you add a single line to the table definition:

CREATE TABLE product (
  product_Id NUMBER,
  product_name VARCHAR2(100),
  price NUMBER(10, 2),
  start_date DATE,
  end_date DATE
  period for valid (start_date, end_date));

This period for valid line indicates that these are the two dates used to determining if a record is valid.

How Can I Use Period Definition In My Queries?

So, now you have the period for valid keyword in your table. How do you actually use it?

You just add a single line into your SELECT statement.

This might have been your query before:

SELECT product_id, product_name, price, start_date, end_date
FROM product
WHERE start_date <= SYSDATE
AND end_date > SYSDATE;

You can change your query to use the AS OF PERIOD FOR VALID syntax:

SELECT product_id, product_name, price, start_date, end_date
FROM product
AS OF PERIOD FOR VALID SYSDATE;

This query will find records where sysdate is between start_date and end_date.

What About Other Dates?

You don't have to use SYSDATE for this query. You can use any date value. For example, records valid in 30 days time:

SELECT product_id, product_name, price, start_date, end_date
FROM product
AS OF PERIOD FOR VALID SYSDATE + 30;

Or, records valid from yesterday:

SELECT product_id, product_name, price, start_date, end_date
FROM product
AS OF PERIOD FOR VALID SYSDATE - 1;

Or, any date you provide to it:

SELECT product_id, product_name, price, start_date, end_date
FROM product
AS OF PERIOD FOR VALID '01-JAN-2015';

As you can see, this is a pretty simple feature, but it can be quite useful if you work on databases that have data in this format.