Oracle 11G – Virtual Columns (Dates)

Posted on by

A couple of days ago there was a question (in Dutch) on the utPLSQL forum about filling up a table with a lot of dates and some info about those dates, like the weeknumber, the dayname etc. I created code using persistent columns at first, so the code would run on a pre-11G installation, but I was thinking about how this could be done using the new Virtual Column option in 11G.

First I wrote a package for the fields that cannot easily be derived from the data field itself, for instance if the current year is a leap year or not. This package holds the following functions:

  • isleapyear
  • isweekend
  • dayname
  • monthname
CREATE OR REPLACE PACKAGE dates_vp
IS
  -- Author  : PBAREL
  -- Purpose : Functions for virtual columns

  -- Public function and procedure declarations
  FUNCTION isleapyear(
    year_in                        IN      NUMBER )
    RETURN VARCHAR2 DETERMINISTIC;

  FUNCTION isweekend(
    daynumber_in                   IN      NUMBER )
    RETURN VARCHAR2 DETERMINISTIC;

  FUNCTION dayname(
    daynumber_in                   IN      NUMBER )
    RETURN VARCHAR2 DETERMINISTIC;

  FUNCTION monthname(
    monthnumber_in                 IN      NUMBER )
    RETURN VARCHAR2 DETERMINISTIC;
END dates_vp;

I know that the day name and the month name can probably be derived using an overload of the to_char function, but this way I could rather easily make it work.

The implementation is as follows:

CREATE OR REPLACE PACKAGE BODY dates_vp
IS
  -- Private constant declarations
  c_unknown                       CONSTANT VARCHAR2( 10 ) := 'Unknown';
  c_yes                           CONSTANT VARCHAR2( 3 ) := 'Yes';
  c_no                            CONSTANT VARCHAR2( 3 ) := 'No';
  c_sunday                        CONSTANT VARCHAR2( 10 ) := 'Sunday';
  c_monday                        CONSTANT VARCHAR2( 10 ) := 'Monday';
  c_tuesday                       CONSTANT VARCHAR2( 10 ) := 'Tuesday';
  c_wednesday                     CONSTANT VARCHAR2( 10 ) := 'Wednesday';
  c_thursday                      CONSTANT VARCHAR2( 10 ) := 'Thursday';
  c_friday                        CONSTANT VARCHAR2( 10 ) := 'Friday';
  c_saturday                      CONSTANT VARCHAR2( 10 ) := 'Saturday';
  c_january                       CONSTANT VARCHAR2( 10 ) := 'January';
  c_february                      CONSTANT VARCHAR2( 10 ) := 'February';
  c_march                         CONSTANT VARCHAR2( 10 ) := 'March';
  c_april                         CONSTANT VARCHAR2( 10 ) := 'April';
  c_may                           CONSTANT VARCHAR2( 10 ) := 'May';
  c_june                          CONSTANT VARCHAR2( 10 ) := 'June';
  c_july                          CONSTANT VARCHAR2( 10 ) := 'July';
  c_august                        CONSTANT VARCHAR2( 10 ) := 'August';
  c_september                     CONSTANT VARCHAR2( 10 ) := 'September';
  c_october                       CONSTANT VARCHAR2( 10 ) := 'October';
  c_november                      CONSTANT VARCHAR2( 10 ) := 'November';
  c_december                      CONSTANT VARCHAR2( 10 ) := 'December';

  -- Function and procedure implementations
  PROCEDURE initialization
  IS
  BEGIN
    NULL;
  END initialization;

  FUNCTION isleapyear(
    year_in                        IN      NUMBER )
    RETURN VARCHAR2 DETERMINISTIC
  IS
    l_dummy                                  DATE;
  BEGIN
    l_dummy   := TO_DATE( '2902' || TO_CHAR( year_in ), 'DDMMYYYY' );
    RETURN c_yes;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN c_no;
  END;

  FUNCTION isweekend(
    daynumber_in                   IN      NUMBER )
    RETURN VARCHAR2 DETERMINISTIC
  IS
    l_returnvalue                            VARCHAR2( 3 );
  BEGIN
    IF    ( daynumber_in = 1 )
       OR ( daynumber_in = 7 ) THEN
      l_returnvalue   := c_yes;
    ELSE
      l_returnvalue   := c_no;
    END IF;

    RETURN l_returnvalue;
  END;

  FUNCTION dayname(
    daynumber_in                   IN      NUMBER )
    RETURN VARCHAR2 DETERMINISTIC
  IS
    l_returnvalue                            VARCHAR2( 20 );
  BEGIN
    CASE daynumber_in
      WHEN 1 THEN l_returnvalue := c_sunday;
      WHEN 2 THEN l_returnvalue := c_monday;
      WHEN 3 THEN l_returnvalue := c_tuesday;
      WHEN 4 THEN l_returnvalue := c_wednesday;
      WHEN 5 THEN l_returnvalue := c_thursday;
      WHEN 6 THEN l_returnvalue := c_friday;
      WHEN 7 THEN l_returnvalue := c_saturday;
      ELSE
        l_returnvalue := c_unknown;
    END CASE;
    RETURN l_returnvalue;
  END;

  FUNCTION monthname(
    monthnumber_in                 IN      NUMBER )
    RETURN VARCHAR2 DETERMINISTIC
  IS
    l_returnvalue                            VARCHAR2( 20 );
  BEGIN
    CASE monthnumber_in
      WHEN 1 THEN l_returnvalue := c_january;
      WHEN 2 THEN l_returnvalue := c_february;
      WHEN 3 THEN l_returnvalue := c_march;
      WHEN 4 THEN l_returnvalue := c_april;
      WHEN 5 THEN l_returnvalue := c_may;
      WHEN 6 THEN l_returnvalue := c_june;
      WHEN 7 THEN l_returnvalue := c_july;
      WHEN 8 THEN l_returnvalue := c_august;
      WHEN 9 THEN l_returnvalue := c_september;
      WHEN 10 THEN l_returnvalue := c_october;
      WHEN 11 THEN l_returnvalue := c_november;
      WHEN 12 THEN l_returnvalue := c_december;
      ELSE
        l_returnvalue := c_unknown;
    END CASE;
    RETURN l_returnvalue;
  END;
BEGIN
  initialization;
END dates_vp;

Note the function to decide whether we are in a leap year or not. It tries to convert the 29th of February to a real date. If it fails, we are not in a leap year, if it succeeds, then the current year is a leap year. Using the exception being raised by Oracle allows me to use Oracle to decide instead of writing the entire algorithm myself.

Then it was time to create the table, a sequence and create the code to fill up the 2 column table.

CREATE TABLE dates_vc(
  thekey NUMBER
, thedate DATE
);


CREATE SEQUENCE dates_vc_seq START WITH 1 NOCACHE;


DECLARE
  l_startdate DATE;
  l_enddate DATE;
  l_currentdate DATE;
  l_startyear PLS_INTEGER;
  l_startmonth PLS_INTEGER;
  l_startday PLS_INTEGER;
  l_endyear PLS_INTEGER;
  l_endmonth PLS_INTEGER;
  l_endday PLS_INTEGER; 
BEGIN
  l_startdate := TO_DATE('01011960','DDMMYYYY');
  l_enddate := TO_DATE('31122020,'DDMMYYYY');
  l_startyear := TO_CHAR(l_startdate,'YYYY');
  
  l_endyear := TO_CHAR(l_enddate,'YYYY');
  
  FOR yearindex IN l_startyear .. l_endyear LOOP
    IF yearindex = l_startyear THEN
      l_startmonth := TO_CHAR(l_startdate,'MM');
      l_startday := TO_CHAR(l_startdate,'DD');
    ELSE
      l_startmonth := 1;
      l_startday := 1;
    END IF;
    IF yearindex = l_endyear THEN
      l_endmonth := TO_CHAR(l_enddate,'MM');
      l_endday := TO_CHAR(l_enddate,'DD');
    ELSE
      l_endmonth := 12;
      l_endday   := 31;
    END IF;
    
    FOR monthindex IN l_startmonth .. l_endmonth LOOP
      FOR dayindex IN l_startday .. l_endday LOOP
        BEGIN
          l_currentdate := TO_DATE(to_char(dayindex)||'/'
                                 ||to_char(monthindex)||'/'
                                 ||to_char(yearindex)
                                  , 'DD/MM/YYYY');
          INSERT INTO dates_vc (thekey, thedate) VALUES 
                               (dates_vc_seq.nextval, l_currentdate);
        EXCEPTION
          WHEN OTHERS THEN 
            NULL;
        END;
      END LOOP;
    END LOOP;
    COMMIT;
  END LOOP;
END;
/

I know, I shouldn’t use the WHEN OTHERS THEN NULL; exception, but in this case, if conversion of the date fails, then that’s ok, just continue with the next possibility.
Now that the table with the persistent columns is in place, it’s time to add the Virtual Columns to the table so I can select everything I want.

ALTER TABLE dates_vc 
  ADD (thedaynr AS (to_number(to_char(thedate, 'DDD'))));
ALTER TABLE dates_vc 
  ADD (theday AS ( dates_vp.dayname(to_number(to_char(thedate, 'D')))));
ALTER TABLE dates_vc 
  ADD (theweek AS (to_number(to_char(thedate, 'IW'))));
ALTER TABLE dates_vc 
  ADD (themonthnr AS (to_number(to_char(thedate, 'MM'))));
ALTER TABLE dates_vc 
  ADD (themonth AS (dates_vp.monthname(to_number(to_char(thedate, 'MM')))));
ALTER TABLE dates_vc 
  ADD (thequarter AS (FLOOR(( to_number(to_char(thedate, 'MM')) + 2 ) / 3 )));
ALTER TABLE dates_vc 
  ADD (leapyear AS (dates_vp.isleapyear(to_number(TO_char(thedate,'YYYY')))));
ALTER TABLE dates_vc 
  ADD (weekend AS (dates_vp.isweekend(TO_NUMBER(to_char(thedate,'DDD')))));
ALTER TABLE dates_vc 
  ADD (theyearmonth AS (to_number(to_char(thedate,'YYYYMM'))));

Now that everything is in place, let’s select a row from the table. Let’s take a completely random date (December 29th 1972, my birthdate):

SELECT *
  FROM dates_vc
 WHERE dates_vc.thedate = TO_DATE( '12291972', 'MMDDYYYY' )

The results are as follows:

THEKEY 4747 Persistent
THEDATE 12/29/1972 Persistent
THEDAYNR 364 Virtual
THEDAY Friday Virtual
THEWEEK 52 Virtual
THEMONTHNR 12 Virtual
THEMONTH December Virtual
THEQUARTER 4 Virtual
LEAPYEAR Yes Virtual
WEEKEND No Virtual
THEYEARMONTH 197212 Virtual

There you have it. Just 2 persistent columns, a couple of functions and a lot of virtual columns. It takes up the minimum of space in the tables, the rest of the columns is derived from the persistent date column. And I think this is fast enough, even though the virtual columns are calculated at runtime. But, since the functions are deterministic, I think Oracle has the results cached some way or the other. I read once that data that can easily be derived from other data should not be stored in the table, except maybe for speed reasons, but using virtual columns you can virtually store the columns in the database, so it seems to other people as if you store all the values, where you actually (or have Oracle) calculate the derived values.

I know it’s a rather useless issue, but it is fun to play with this new functionality.

2 thoughts on “Oracle 11G – Virtual Columns (Dates)”

Leave a Reply to Alex Nuijten Cancel reply

Your email address will not be published. Required fields are marked *