For Better, Faster, Smarter
Oracle Consulting and Training
Oracle 11g SQL New Features for Data Warehouses
In our series of articles on the Oracle 11g new features we've highlighted a few of the more interesting ones including
new features in SQL and SQL*Plus here we introduce two of the new features for data warehouses/Business
the new pivot and unpivot clauses for SELECT statements.
Virtual columns are read-only columns whose values are derived from one or more other columns in the same table
by the underlying expression in the column definition. As with normal table columns they can be indexed but, unlike
other columns, virtual columns are not stored - their value is calculated at run time. This has two advantages over
using a PL/SQL trigger:
better performance as there is no context switching between SQL
faster development as less coding is required
Virtual Column Example
We’ll look at an example in to show how to create a table with a
virtual column in Oracle 11g.
Let's suppose we’re tracking sales made by employee and we want to
categorise transactions accoding to the value of the sale. If the value of
the sale is less than £1000 that’s classified as low, between £1000 and
£4999 is medium and greater than £5000 is high.
Here is the create table statement:
CREATE TABLE sales (
WHEN order_value < 1000 THEN ‘LOW’
WHEN order_value >= 1000 AND order_value < 5000 THEN ‘MEDIUM’
Notice that the order_category column references order_value in an expression. This makes it a virtual column.
Once you've created your table it looks just like any other table on the surface. Using DESCRIBE in SQL*Plus or
querying user_tables for the table definition does not show that a column is virtual.
SQL> desc sales
Name Null? Type
----------------------- --------- --------
However DATA_DEFAULT in USER_TAB_COLUMNS/ DBA_TAB_COLUMNS does hold the definition of a
Restrictions On Virtual Columns in Oracle 11g SQL
Virtual columns are read only so an attempt to write to a virtual column generates an error:
ORA-54013 for insert statements
ORA-54017 for update statements.
Whilst you can have more than one virtual column in a table, you can’t nest them, so a virtual column definition can
only reference a real column.
The use of group functions (max, min, avg, sum etc ) in a virtual column definition is not allowed either. Attempting
to use one of those generates the error ORA-00934: group function is not allowed here
Virtual columns can be referenced 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 SQL:
INSERT INTO sales VALUES (1,1,1, SYSDATE, 10519, DEFAULT);
There is no support for virtual columns in temporary, 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.
Virtual columns can be indexed - which makes it the equivalent of a function-based index - 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
In earlier versions of the Oracle database it is possible to use the CASE or DECODE statements to pivot rows into
columns but it is not easy to convert columns into rows. To do this you have to combine the output of multiple SQL
statements with the UNION clause.
Oracle 11g SQL makes this easy for us - we can now use the new PIVOT clause of the SELECT statement to change
rows into columns and the new UNPIVOT clause to change columns into rows,
Here’s an example of each using our sales table has the following structure and data:
EMP_ID CUST_ID MONTH AMT
---------- ---------- ----------- -----------
1 1 01-JAN-2014 16.5
1 2 01-JAN-2014 112.7
1 2 01-FEB-2014 37
1 1 01-FEB-2014 11
2 1 01-JAN-2014 17
2 1 01-JAN-2014 16.4
2 1 01-FEB-2014 9.4
2 2 01-JAN-2014 19.8
2 2 01-JAN-2014 12.8
2 2 01-FEB-2014 29.6
Presently our data has 1 month per row but if we want to have just 1 row per customer/employee combination with the
months as columns we can use the new PIVOT clause in the select statement in Oracle 11g SQL:-
SELECT * FROM
(SELECT emp_id,month,cust_id,amt FROM sales)
PIVOT (SUM(amt) FOR month IN ('01-JAN-14','01-FEB-14'))
ORDER BY emp_id
to produces these results:
EMP_ID CUST_ID '01-JAN-14' '01-FEB-14'
---------- ---------- ------------ -----------
1 1 16.5 11
1 2 112.7 37
2 1 33.4 9.4
2 2 32.6 29.6
By using the UNPIVOT clause we can convert the results of our first example back to its original form. We'll create a
pivot table of our sales data first with the monthly sales amounts called ‘jan-14’ and ‘feb-14’.
CREATE TABLE pivot_sales AS SELECT * FROM
(SELECT emp_id,month,cust_id,amt FROM sales) PIVOT
(SUM(amt) FOR month IN ('01-JAN-14' as jan_14,'01-FEB-14' as feb_14))
Next we use the unpivot clause to convert the monthly sales data columns back into rows.
SQL> SELECT * FROM pivot_sales UNPIVOT (amt FOR month IN (jan_14,feb_14))
2 ORDER BY emp_id, cust_id, to_date(month,’mon-yy’);
EMP_ID CUST_ID MONTH AMT
---------- ---------- ------ ----------
1 1 JAN_14 16.5
1 1 FEB_14 11
1 2 JAN_14 112.7
1 2 FEB_14 37
2 1 JAN_14 33.4
2 1 FEB_14 9.4
2 2 JAN_14 32.6
2 2 FEB_14 29.6
8 rows selected.
Looking to sky-rocket productivity, save time and reduce costs?Training is a highly cost-effective and proven
method of boosting productivity. Smartsoft offers instructor-led training in Oracle and related technologies on or off
site in cities across the UK as well as self-study online training. See our scheduled Oracle training courses, or tell us
Oracle tips and tricks Subscribe to our newsletter, jam-packed full of tips and tricks to help you slash costs, sky-rocket
productivity and make your systems better, faster and smarter.
© Smartsoft Computing Ltd, Bristol, England
Need help? Contact Us
This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such
as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.