 |
|
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
- 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. |
