Oracle 11G – Virtual Columns (Dates)

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 JessicaSwoNi Cancel reply

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