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 Intelligence systems: • virtual columns • the new pivot and unpivot clauses for SELECT statements.

Virtual Columns

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 and PL/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 ( order_id        NUMBER ,employee_id    NUMBER ,item_id        NUMBER ,date_of_sale   DATE ,order_value    NUMBER ,order_category VARCHAR2(20) AS  (     CASE         WHEN order_value < 1000 THEN ‘LOW’         WHEN order_value >= 1000 AND order_value < 5000 THEN ‘MEDIUM’         ELSE ‘HIGH’     END     ) ); 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 ----------------------- --------- -------- ORDER_ID                           NUMBER EMPLOYEE_ID                        NUMBER ITEM_ID                            NUMBER DATE_OF_SALE                       DATE ORDER_VALUE                        NUMBER ORDER_CATEGORY                     VARCHAR2(20) However DATA_DEFAULT in USER_TAB_COLUMNS/ DBA_TAB_COLUMNS does hold the definition of a virtual column.

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

PIVOT clause

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

UNPIVOT clause

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 your requirements. 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 woopra privacy policy.     View Smartsoft privacy policy Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.