Oracle 11g SQL New Features (part 2)

For this series of articles on Oracle 11g SQL new features we've picked out a few of the more interesting ones to investigate and illustrate in depth.

The previous article in this series about the Oracle 11g SQL new features covered SQL*Plus new features and the enhancements to regular expressions in SQL and PL/SQL. Let's continue by looking at some of the Data warehousing enhancements.


Oracle 11g SQL New Features for Data Warehouses

There are a couple of interesting new features in this area - virtual columns and the new pivot and unpivot clauses for SELECT statements. Let's start with virtual columns .

Oracle Tips & Tricks to SKYROCKET Your Career!

If you're not already a subscriber you're  missing out on a myriad of tips and techniques to help you become a better, faster, smarter developer. Subscribe now and ignite your career.

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_idsalaryinvestment_amtshare_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 inuser_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 statements. 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 columns 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 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. 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 columnsTo 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 Training

With our partners we offer scheduled, instructor-led Oracle training on and off site in the UK and US as well as on-line training on demand. See here for more information.


Return to Oracle 11g new features overview