Oracle 11g SQL New Features

Virtual Columns

You might be wondering what on earth are virtual columns. Well the short answer is that virtual columns contain data derived from one or more other columns in the table. This means that you can read from them but you can't write to them directly either when inserting or updating a table. That makes sense - if the value is derived, why would you want to update it directly? Apart from potentially saving on coding, the other benefit of virtual columns is that they don't take up any space as they are calculated on the fly.

Let's look at an example using Oracle 11g SQL to see how they work. Let's suppose in our mythical organisation every employee invests a fixed 1% of their salary every month into an employee share option scheme and let's also suppose that we want to keep the history of the amount invested, the price of a share and the number of shares bought every month for each employee. The columns we would want in our table therefore are employee_id, salary, investment_amt, share_price and no_shares. All of which are numbers. So let's create a table with these columns

create table share_option_history (
employee_id number
,pay_day date
,salary number
,investment_amt number AS (salary*0.01)
,share_price number
,num_shares number AS (salary*0.01*share_price)
)

Notice that the columns investment_amt and num_shares both reference salary in an expression. This means that these two columns are virtual columns.

You might wonder why the column num_shares doesn't just reference investment_amt as that already contains the data we need. Unfortunately we can't do that - virtual columns can only reference real columns.

Neither can we use group functions in our expressions so functions such as max, min, avg, sum etc attempting to do this generates the following error ORA-00934: group function is not allowed here.

Once you've created your table with virtual columns what does it look like? Actually, just like any other table!

SQL> desc share_option_history
Name Null? Type
------------------------------------- --------- ----------------
EMPLOYEE_ID NUMBER
PAY_DAY DATE
SALARY NUMBER
INVESTMENT_AMT NUMBER
SHARE_PRICE NUMBER
NUM_SHARES NUMBER

There is no way to tell from describing the table or by querying user_tables in the data_dictionary that the table has virtual columns. However you can tell by looking at the value of DATA_DEFAULT in user_tab_columns or dba_tab_columns - this contains the formula for the specified column if it's a virtual column.

The other way to tell is to provide a value for the column in an update or insert statement. Any attempt to write to a virtual column directly generates an error, either ORA-54013 for insert statments or ORA-54017 for update statments. You can still address virtual columns in update and insert statements but the value provided must be the keyword DEFAULT.

Therefore to insert data into our table we could write a statement like this in Oracle 11g SQL:

INSERT INTO share_option_history VALUES (1, SYSDATE, 105190, DEFAULT, 0.98, DEFAULT);

There are a few restrictions on virtual colums that are worth noting. We've already mentioned that you can't write to them directly, there is also no support for their use in temporay, object, external or cluster tables nor can they be an Oracle supplied data type, a user defined type, a LOB or a LONG RAW and the values of virtual columns must be scalar. The good news though is that you can create indexes on them - in which case they are equivalent to function-based indexes - and they can reference user-defined functions as long as the functions are specified as DETERMINISTIC (i.e the same inputs will always produce the same outputs).

Pivot and Unpivot Clauses

These two new clauses of the SELECT statement in Oracle 11g SQL enable you to switch rows into columns and columns into rows respectively. Through the use of CASE statements and, in earlier versions of Oracle, the DECODE statement we've always been able to pivot rows into columns but it's not been so easy to convert columns into rows you will have had to resort to connecting multiple SQL statements with the UNION clause.

To illustrate how these work, let's assume we have a table holding our sales details with the following structure and data:

EMP_ID CUST_ID MONTH AMT
---------- ---------- ----------- -----------
1    1 01-JAN-2009 10.5
1 2 01-JAN-2009 12.7
1   2 01-FEB-2009 12.7
1   1 01-FEB-2009    11
1   1 01-FEB-2009    19
1   1 01-FEB-2009    19
2    1 01-JAN-2009 17
2    1 01-JAN-2009 16.4
2   1 01-FEB-2009    9.4
2 2 01-JAN-2009    9.4
2    2 01-JAN-2009 12.8
2 2 01-FEB-2009    19.6

PIVOT clause

In our sales table, every month is a different row, but let's suppose we want to have just 1 row per customer/employee combination with the months as columns. To do this we can use the PIVOT clause in the select statement in Oracle 11g SQL like this:-

SELECT * FROM
(SELECT emp_id,month,cust_id,amt FROM sales)
PIVOT (SUM(amt) FOR month IN ('01-JAN-09','01-FEB-09'))
ORDER BY emp_id

And get the following results:

EMP_ID CUST_ID '01-JAN-09' '01-FEB-09'
---------- ---------- ------------ -----------
1    1 10.5 49
    1 2 12.7 12.7
2   1 33.4 9.4
2 2 22.2 29

UNPIVOT clause

With the unpivot clause we can convert the monthly sales amount columns into rows. To illustrate how this works we'll create a pivot table of our sales data first.

CREATE TABLE pivot_sales AS SELECT * FROM
(
SELECT emp_id,month,cust_id,amt FROM sales)
PIVOT (SUM(amt) FOR month IN ('01-JAN-09' as mth_1,'01-FEB-09' as mth_2)
)

This gives us the same data as before but the column names for the monthly sales amounts are MTH_1 and MTH_2.

To unpivot the data we use the unpivot cluse like so:-
SELECT * FROM pivot_sales UNPIVOT (amt FOR month IN (MTH_1,MTH_2))
ORDER BY emp_id, cust_id

This produces the following results:

EMP_ID CUST_ID MONTH     AMT
---------- ---------- ------ -----------
1    1 MTH_2    49
1    1 MTH_1    10.5
1    2 MTH_1    12.7
1    2 MTH_2    12.7
2    1 MTH_2    9.4
2    1 MTH_1    33.4
2    2 MTH_1    22.2
2    2 MTH_2    29

Oracle 11g Training

With our partners we offer instructor-led Oracle training on and off site in the UK and New Zealand as well as training via the Internet.

See here for Oracle training in NZ or here for Oracle training in the UK. Or contact us for details of our self-led on-line training courses.

Oracle 11g SQL New Features (ctd)