BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

11g SQL New Features

Oracle 11g New Features Tips by Burleson Consulting
June 27, 2008

Oracle 11g New Features Tips

This chapter examines the SQL enhancements in the 11g database, including those that can improve execution efficiency by 200% (Oracle’s Results), along with needed additions to Regular Expressions.  The new features include:

  • PIVOT and UNPIVOT Operators
  • Regular Expressions
  • The /*+result_cache*/ SQL hint
  • Added built-in functions to support advanced statistics added in 11g and are covered in detail in the documentation.

SQL PIVOT and UNPIVOT Operators

Pivoting databases consists of taking a result set and turning columns into rows.  Spreadsheet users have long had the ability to PIVOT data with ease.  Here is another example of PIVOT in use by displaying books sold by month:

SELECT
  EXTRACT(MONTH FROM order_date) Dates,
  book_key,
  sum(quantity)
FROM sales
where book_key in ('B101','B102')
group by EXTRACT(MONTH FROM order_date), book_key
order by EXTRACT(MONTH FROM order_date);

     DATES BOOK_K SUM(QUANTITY)
---------- ------ -------------
         1 B101            1000
         1 B102            2590
         2 B101             100
         2 B102             200
         3 B102             900
         4 B101             300
         4 B102            7100
         5 B101           16800
         5 B102            5500

While the needed data is returned, it may be more appropriate to have the book_key as columns rather than rows.  In SQL, pivoting data used to entail a large, complicated SQL statement.  This was made up of multiple DECODE/CASE operations.  Luckily, Oracle has added the PIVOT and UNPIVOT operators to make this process less complicated, and less prone to error.

Using PIVOT will reorder the values of rows into columns, and execute the aggregate functions.

PIVOT (<aggregate function> (expression) [AS <alias>]
 FOR (<column_list>)
 IN <subquery>)

Here is the same query using the PIVOT operator to move the book_keys from rows to columns.  Notice that the aggregate is moved into the PIVOT clause:

SELECT *
FROM (SELECT
        EXTRACT(MONTH FROM order_date) Dates,
        book_key,
        quantity
      FROM sales)
PIVOT (SUM(quantity)
FOR book_key
IN ('B101','B102'))
order by 1;

DATES     'B101'     'B102'
---------- ---------- ----------
         1       1000       2590
         2        100        200
         3                   900
         4        300       7100
         5      16800       5500


Above it can be seen that the rows are still listed on month, but the summed quantities are now the data points with the book_keys columns.

While the PIVOT operator moves data from rows to columns, the UNPIVOT operator moves data in the opposite direction; from columns to rows.

UNPIVOT [<INCLUDE | EXCLUDE> NULLS] (<column_list>) FOR (<column_list>) IN (<column_list>)

Here, the UNPIVOT defaults to exclude NULLS, but there is an option to include them.  Furthermore, the first two-column list defines the names for the columns created by the data move, from columns to rows.  The example below selects data from the SALES table:

SELECT
  order_date,
  book_key,
  store_key,
  quantity
FROM sales
where book_key in ('B101','B102')
order by 1;

ORDER_DAT BOOK_K STOR   QUANTITY
--------- ------ ---- ----------
02-JAN-04 B102   S102         10
02-JAN-04 B101   S101       1000
02-JAN-04 B102   S103        200
03-JAN-04 B102   S104        400
03-JAN-04 B102   S105        800
04-JAN-04 B102   S110        160
04-JAN-04 B102   S109       1020
12-FEB-04 B102   S103        200
12-FEB-04 B101   S103        100
25-MAR-04 B102   S107        900
02-APR-04 B101   S110        300

02-APR-04 B102   S110       1900
26-APR-04 B102   S107       5200
17-MAY-04 B101   S105       8000
18-MAY-04 B102   S106       5500
20-MAY-04 B101   S104       8800

16 rows selected.

The Quantity column can be moved into the rows by using the UNPIVOT operator.

select *
FROM (SELECT
        order_date,
        book_key,
        store_key,
        quantity
      FROM sales
      where book_key in ('B101','B102'))
   UNPIVOT (Qty FOR Type IN (quantity))
 order by 1;

ORDER_DAT BOOK_K STOR TYPE            QTY
--------- ------ ---- -------- ----------
02-JAN-04 B102   S102 QUANTITY         10
02-JAN-04 B101   S101 QUANTITY       1000
02-JAN-04 B102   S103 QUANTITY        200
03-JAN-04 B102   S104 QUANTITY        400
03-JAN-04 B102   S105 QUANTITY        800
04-JAN-04 B102   S110 QUANTITY        160
04-JAN-04 B102   S109 QUANTITY       1020
12-FEB-04 B102   S103 QUANTITY        200
12-FEB-04 B101   S103 QUANTITY        100
25-MAR-04 B102   S107 QUANTITY        900
02-APR-04 B101   S110 QUANTITY        300
02-APR-04 B102   S110 QUANTITY       1900
26-APR-04 B102   S107 QUANTITY       5200
17-MAY-04 B101   S105 QUANTITY       8000
18-MAY-04 B102   S106 QUANTITY       5500
20-MAY-04 B101   S104 QUANTITY       8800

16 rows selected.

Using careful observation, it can be seen that the QUANTITY column has been moved into the TYPE column and added to each row.  Subsequently, the quantity values have been moved to the QTY column.  This operator becomes even more useful upon attempting unpivot on multiple columns.  Each column unpivoted will generate another row in the result set.  In the example below, the usefulness of this function in analyzing data is seen by adding an additional constant column to the example queries above.

SELECT
  order_date,
  book_key,
  store_key,
  quantity,
  5 as OddNumber
FROM sales
where book_key in ('B101','B102')
order by 1; 

ORDER_DAT BOOK_K STOR   QUANTITY  ODDNUMBER
--------- ------ ---- ---------- ----------
02-JAN-04 B102   S102         10          5
02-JAN-04 B101   S101       1000          5
02-JAN-04 B102   S103        200          5
03-JAN-04 B102   S104        400          5
03-JAN-04 B102   S105        800          5
04-JAN-04 B102   S110        160          5
04-JAN-04 B102   S109       1020          5
12-FEB-04 B102   S103        200          5
12-FEB-04 B101   S103        100          5
25-MAR-04 B102   S107        900          5
02-APR-04 B101   S110        300          5
02-APR-04 B102   S110       1900          5
26-APR-04 B102   S107       5200          5
17-MAY-04 B101   S105       8000          5
18-MAY-04 B102   S106       5500          5
20-MAY-04 B101   S104       8800          5

16 rows selected.

Now the UNPIVOT operator can be used to move the QUANTITY and ODDNUMBER columns into data rows.

select *
FROM (SELECT
        order_date,
        book_key,
        store_key,
        quantity,
        5 as num
      FROM sales
      where book_key in ('B101','B102'))
   UNPIVOT (Num FOR Type IN (quantity, num))
 order by 1;

ORDER_DAT BOOK_K STOR TYPE            NUM
--------- ------ ---- -------- ----------
02-JAN-04 B102   S102 NUM               5
02-JAN-04 B102   S102 QUANTITY         10
02-JAN-04 B101   S101 NUM               5
02-JAN-04 B101   S101 QUANTITY       1000
02-JAN-04 B102   S103 QUANTITY        200
02-JAN-04 B102   S103 NUM               5
03-JAN-04 B102   S104 QUANTITY        400
03-JAN-04 B102   S104 NUM               5
03-JAN-04 B102   S105 QUANTITY        800
03-JAN-04 B102   S105 NUM               5
04-JAN-04 B102   S110 QUANTITY        160
04-JAN-04 B102   S110 NUM               5
04-JAN-04 B102   S109 QUANTITY       1020
04-JAN-04 B102   S109 NUM               5
12-FEB-04 B102   S103 QUANTITY        200
12-FEB-04 B102   S103 NUM               5
12-FEB-04 B101   S103 QUANTITY        100
12-FEB-04 B101   S103 NUM               5
25-MAR-04 B102   S107 QUANTITY        900
25-MAR-04 B102   S107 NUM               5
02-APR-04 B101   S110 QUANTITY        300
02-APR-04 B101   S110 NUM               5
02-APR-04 B102   S110 QUANTITY       1900
02-APR-04 B102   S110 NUM               5
26-APR-04 B102   S107 QUANTITY       5200
26-APR-04 B102   S107 NUM               5
17-MAY-04 B101   S105 QUANTITY       8000
17-MAY-04 B101   S105 NUM               5
18-MAY-04 B102   S106 QUANTITY       5500
18-MAY-04 B102   S106 NUM               5
20-MAY-04 B101   S104 QUANTITY       8800
20-MAY-04 B101   S104 NUM               5

32 rows selected.

Once the data is organized for easy manipulation, queries can be run against the results of the UNPIVOT, such as below:

select book_key, store_key, sum(Num)
FROM (SELECT
        order_date,
        book_key,
        store_key,
        quantity,
        5 as num
      FROM sales
      where book_key in ('B101','B102'))
   UNPIVOT (Num FOR Type IN (quantity, num))
where type = 'NUM'
group by book_key, store_key
order by 1;

BOOK_K STOR   SUM(NUM)
------ ---- ----------
B101   S101          5
B101   S103          5
B101   S104          5
B101   S105          5
B101   S110          5
B102   S102          5
B102   S103         10
B102   S104          5
B102   S105          5
B102   S106          5
B102   S107         10
B102   S109          5
B102   S110         10 

13 rows selected.

Both the PIVOT and UNPIVOT operators can be used to organize data for further analysis, or to generate reports that are easier to read and understand.  The PIVOT and UNPIVOT operators are typically more efficient at generating a result set than the same query using the traditional DECODE/CASE operators.

 

This is an excerpt from the new book Oracle 11g New Features: Expert Guide to the Important New Features by John Garmany, Steve Karam, Lutz Hartmann, V. J. Jain, Brian Carr.

You can buy it direct from the publisher for 30% off.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter