PIVOT Query in SQL

Introduction

This is a very simple example of Pivot query for the beginners. We use pivot queries when we need to transform data from row-level to columnar data.

Pivot query help us to generate an interactive table that quickly combines and compares large amounts of data. We can rotate its rows and columns to see different summaries of the source data, and we can display the details for areas of interest at a glance. It also help us to generate Multidimensional reporting.

Background

This post intends to help T-SQL developers get started with PIVOT queries. Most business applications will need some sort of PIVOT queries and I am sure many of you must have come across pivoting requirements several times in the past. 

Using the Code

Let us have a table name Invoice which has three properties, InvoiceNumber, InvoiceDate,InvoiceAmount. Suppose we have several rows input in the table. Our goal is to display the sum ofInvoiceAmount each month.

SELECT * FROM (SELECT year(invoiceDate) as [year], left(datename(month,invoicedate),3)as [month], _
InvoiceAmount as Amount FROM Invoice) as InvoiceResult 

SELECT *
FROM (
    SELECT 
        year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month], 
        InvoiceAmount as Amount 
    FROM Invoice
) as s
PIVOT
(
    SUM(Amount)
    FOR [month] IN (jan, feb, mar, apr, 
    may, jun, jul, aug, sep, oct, nov, dec)
)AS pivot

How to handle BULKCOLLECT Exceptions in PL/SQL

Since Oracle 9i the FORALL statement includes an optional SAVE EXCEPTIONS clause that allows bulk operations to save exception information and continue processing.  Once the operation is complete, the exception information can be retrieved using the SQL%BULK_EXCEPTIONS attribute.  This is a collection of exceptions for the most recently executed FORALL statement, with the following two fields for each exception:

SQL%BULK_EXCEPTIONS(i).ERROR_INDEX – Holds the iteration (not the subscript) of the original FORALL statement that raised the exception.  In sparsely populated collections, the exception row must be found by looping through the original collection the correct number of times.

SQL%BULK_EXCEPTIONS(i).ERROR_CODE – Holds the exceptions error code.

The total number of exceptions can be returned using the collections COUNT method, which returns zero if no exceptions were raised.  The save_exceptions.sql script, a modified version of the handled_exception.sql script, demonstrates this functionality.

save_exceptions.sql

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF exception_test%ROWTYPE;

  l_tab          t_tab := t_tab();
  l_error_count  NUMBER; 

  ex_dml_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);
BEGIN
  -- Fill the collection.
  FOR i IN 1 .. 100 LOOP
    l_tab.extend;
    l_tab(l_tab.last).id := i;
  END LOOP;

  -- Cause a failure.
  l_tab(50).id := NULL;
  l_tab(51).id := NULL; 

  EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';

  -- Perform a bulk operation.
  BEGIN
    FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS
      INSERT INTO exception_test
      VALUES l_tab(i);
  EXCEPTION
    WHEN ex_dml_errors THEN
      l_error_count := SQL%BULK_EXCEPTIONS.count;
      DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
      FOR i IN 1 .. l_error_count LOOP
        DBMS_OUTPUT.put_line('Error: ' || i ||
          ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
          ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
      END LOOP;
  END;
END;
/

SET ECHO ON
SELECT COUNT(*)
FROM   exception_test;
SET ECHO OFF

The FORALL statement includes the SAVE EXCEPTIONS clause, and the exception handler displays the number of exceptions and their associated error messages.  The output from the save_exceptions.sql script is listed below.

SQL> @save_exceptions.sql

Number of failures: 2
Error: 1 Array Index: 50 Message: ORA-01400: cannot insert NULL into ()
Error: 2 Array Index: 51 Message: ORA-01400: cannot insert NULL into ()

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*)
  2  FROM   exception_test;

  COUNT(*)
----------
        98

1 row selected.

SQL> SET ECHO OFF

As expected the test table contains 98 of the 100 records, and the associated error message has been displayed by looping through the SQL%BULK_EXCEPTION collection.

If the SAVE EXCEPTIONS clause is omitted from the FORALL statement, execution of the bulk operation stops at the first exception and the SQL%BULK_EXCEPTIONS collection contains a single record.  The no_save_exceptions.sql script demonstrates this behavior.

no_save_exceptions.sql

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF exception_test%ROWTYPE;

  l_tab          t_tab := t_tab();
  l_error_count  NUMBER; 

  ex_dml_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(ex_dml_errors, -01400);
BEGIN
  -- Fill the collection.
  FOR i IN 1 .. 100 LOOP
    l_tab.extend;
    l_tab(l_tab.last).id := i;
  END LOOP;

  -- Cause a failure.
  l_tab(50).id := NULL;
  l_tab(51).id := NULL; 

  EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';

  -- Perform a bulk operation.
  BEGIN
    FORALL i IN l_tab.first .. l_tab.last
      INSERT INTO exception_test
      VALUES l_tab(i);
  EXCEPTION
    WHEN ex_dml_errors THEN
      l_error_count := SQL%BULK_EXCEPTIONS.count;
      DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
      FOR i IN 1 .. l_error_count LOOP
        DBMS_OUTPUT.put_line('Error: ' || i ||
          ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
          ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
     END LOOP;
  END;
END;
/

SET ECHO ON
SELECT COUNT(*)
FROM   exception_test;
SET ECHO OFF

Notice that in addition to the SAVE EXCEPTIONS clause being removed, the no_save_exceptions.sql script now traps a different error number.  The output from this script is listed below.

SQL> @no_save_exceptions.sql

Number of failures: 1
Error: 1 Array Index: 50 Message: ORA-01400: cannot insert NULL into
("TIM_HALL"."EXCEPTION_TEST"."ID")

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*)
  2  FROM   exception_test;

  COUNT(*)
----------
        49

1 row selected.

SQL> SET ECHO OFF

As expected there is only a single error in the SQL%BULK_EXCEPTIONS collection, and there are only 49 records in the test table as the operation has rolled back to the preceding implicit savepoint.

As shown from previous examples, a move from conventional operations to bulk operations will require a revision of your current exception handling or the desired results may not appear.

The use of bulk operations with dynamic SQL is explained in the next section.

======================================================

SQL%BULK_ROWCOUNT

The SQL%BULK_ROWCOUNT cursor attribute gives granular information about the rows affected by each iteration of the FORALL statement. Every row in the driving collection has a corresponding row in theSQL%BULK_ROWCOUNT cursor attribute.

Difference between BULKCOLLECT and FORALL in Oracle

Bulk collect: is a CLAUSE. is used to fetch the records from the cursor.
Forall: is a STATEMENT. is used to do dml operation of fetched records. 
The body of the FORALL statement is a single DML statement -- an INSERT, UPDATE, or DELETE.

BULK COLLECT is:

"The keywords BULK COLLECT tell the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these keywords in the SELECT 
INTO, FETCH INTO, and RETURNING INTO clauses. 

Here is the syntax:

... BULK COLLECT INTO collection_name[, collection_name] ..."
and FORALL is defined as

FORALL is:

"The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. Although the FORALL statement contains an 
iteration scheme, it is not a FOR loop. 

Its syntax follows:

FORALL index IN lower_bound..upper_bound
   sql_statement;

The index can be referenced only within the FORALL statement and only as a collection subscript. The SQL statement must be an INSERT, UPDATE, or DELETE statement that 
references collection elements. And, the bounds must specify a valid range of consecutive index numbers. The SQL engine executes the SQL statement once for each index 
number in the range."

So there you go. Collections, BULK COLLECT and FORALL are the new features in Oracle 8i, 9i and 10g PL/SQL that can really make a different to you PL/SQL performance. 
Hopefully, if you've not come across these areas before.
----------------------------------------------------------------------------

BULK COLLECT Syntax & Example:

FETCH BULK COLLECT BULK COLLECT INTO 
LIMIT ;
set timing on
DECLARE
  CURSOR a_cur IS 
 SELECT program_id
  FROM airplanes;
BEGIN
   FOR cur_rec IN a_cur LOOP
     NULL;
   END LOOP;
END;
 /
DECLARE
  CURSOR a_cur IS 
 SELECT program_id
  FROM airplanes;
  TYPE myarray IS TABLE OF a_cur%ROWTYPE;
  cur_array myarray;
BEGIN
   OPEN a_cur;
   LOOP
     FETCH a_cur BULK COLLECT INTO cur_array LIMIT 100;
     EXIT WHEN a_cur%NOTFOUND;
   END LOOP;
   CLOSE a_cur;
END;
 /
DECLARE
  CURSOR a_cur IS 
 SELECT program_id
  FROM airplanes;
  TYPE myarray IS TABLE OF a_cur%ROWTYPE;
  cur_array myarray;
BEGIN
   OPEN a_cur;
   LOOP
     FETCH a_cur BULK COLLECT INTO cur_array LIMIT 500;
     EXIT WHEN a_cur%NOTFOUND;
   END LOOP;
   CLOSE a_cur;
END;
 /
DECLARE
  CURSOR a_cur IS 
 SELECT program_id
  FROM airplanes;
  TYPE myarray IS TABLE OF a_cur%ROWTYPE;
  cur_array myarray;
BEGIN
   OPEN a_cur;
   LOOP
     FETCH a_cur BULK COLLECT INTO cur_array LIMIT 1000;
     EXIT WHEN a_cur%NOTFOUND;
   END LOOP;
   CLOSE a_cur;
END;
 /
-- try with a LIMIT clause of 2500, 5000, and 10000. What do you see?
----------------------------------------------------------------------------

FORALL Syntax & Example:

FORALL IN .. 
 
 SAVE EXCEPTIONS;
FORALL IN INDICES OF 
 [BETWEEN AND ]
 
 SAVE EXCEPTIONS;
FORALL IN INDICES OF 
VALUES OF 
 
 SAVE EXCEPTIONS;

FOR INSERT
===========

CREATE TABLE servers2 AS
SELECT *
FROM servers
WHERE 1=2;
DECLARE
  CURSOR s_cur IS 
 SELECT *
  FROM servers;
  TYPE fetch_array IS TABLE OF s_cur%ROWTYPE;
  s_array fetch_array;
BEGIN
   OPEN s_cur;
   LOOP
     FETCH s_cur BULK COLLECT INTO s_array LIMIT 1000;
     FORALL i IN 1..s_array.COUNT
     INSERT INTO servers2 VALUES s_array(i);
     EXIT WHEN s_cur%NOTFOUND;
   END LOOP;
   CLOSE s_cur;
   COMMIT;
END;
 /

FOR UPDATE
===========

SELECTDISTINCT srvr_id
FROM servers2
ORDER BY 1;
DECLARE
  TYPE myarray IS TABLE OF servers2.srvr_id%TYPE
  INDEX BY BINARY_INTEGER;
  d_array myarray;
BEGIN
   d_array(1) := 608;
   d_array(2) := 610;
   d_array(3) := 612;
   FORALL i IN d_array.FIRST .. d_array.LAST
   UPDATE servers2
   SET srvr_id = 0
   WHERE srvr_id = d_array(i);
   COMMIT;
END;
 /
SELECT srvr_id
FROM servers2
WHERE srvr_id = 0;

FOR DELETE
============

set serveroutput on
DECLARE
  TYPE myarray IS TABLE OF servers2.srvr_id%TYPE
  INDEX BY BINARY_INTEGER;
  d_array myarray;
BEGIN
   d_array(1) := 614;
   d_array(2) := 615;
   d_array(3) := 616;
   FORALL i IN d_array.FIRST .. d_array.LAST
   DELETE servers2
   WHERE srvr_id = d_array(i);
   COMMIT;
   FOR i IN d_array.FIRST .. d_array.LAST LOOP
     dbms_output.put_line('Iteration #' || i || ' deleted ' ||
     SQL%BULK_ROWCOUNT(i) || ' rows.');
   END LOOP;
END;
 /
SELECT srvr_id
FROM servers2
WHERE srvr_id IN (614, 615, 616);

====================================================

In this article, I will cover the two most important of these features: BULK COLLECT and FORALL.

• BULK COLLECT: SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval
• FORALL: INSERTs, UPDATEs, and DELETEs that use collections to change multiple rows of data very quickly

You may be wondering what very quickly might mean—how much impact do these features really have? Actual results will vary, depending on the version of Oracle Database you are running and the specifics of your application logic. You can download and run the script to compare the performance of row-by-row inserting with FORALL inserting. On my laptop running Oracle Database 11g Release 2, it took 4.94 seconds to insert 100,000 rows, one at a time. With FORALL, those 100,000 were inserted in 0.12 seconds. Wow!

=============================================================

BULK COLLECT & FORALL vs. CURSOR & FOR-LOOP

After more and more reads about BULK COLLECT and FORALL and their performance improvements I decided to have a closer look on it by myself to see how powerful they really are. So I built a little test-case which inserts all entries from the all_object view into another table. The inserts happens on three different ways:
First way is a simple cursor over the view and a insert in a loop with FETCH into local variables. This way also shows how slow the opening of the cursor itself is.
The second way is a simple FOR – IN LOOP with the insert of the cursor variables.
And, of course, the third way is the way with bulking the rows and inserting them with FORALL so lets see.

So the other table looks like this (three columns are enough for this tests)

SQL> create table temp (owner varchar2(30), name varchar2(30), type varchar2(19));

Table created.

And the three diffrent procedures looks like this

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57

CREATE OR REPLACE PROCEDURE CURSOR_FOR_OPEN_QUERY  IS  l_sOwner VARCHAR2(30);  l_sName VARCHAR2(30);  l_sType VARCHAR2(19);  CURSOR cur IS SELECT owner, object_name name, object_type type FROM all_objects;  BEGIN  dbms_output.put_line('Before CURSOR OPEN: ' || systimestamp);  OPEN cur;  dbms_output.put_line('Before LOOP: ' || systimestamp);  LOOP  FETCH cur INTO l_sOwner, l_sName, l_sType;  IF cur%NOTFOUND THEN  EXIT;  END IF;  INSERT INTO temp values (l_sOwner, l_sName, l_sType);  END LOOP;  CLOSE cur;  dbms_output.put_line('After CURSOR CLOSE: ' || systimestamp);  COMMIT;  END;  /   CREATE OR REPLACE PROCEDURE CURSOR_FOR_QUERY  IS  BEGIN  dbms_output.put_line('Before CURSOR: ' || systimestamp);  FOR cur IN (SELECT owner, object_name name, object_type type FROM all_objects) LOOP  INSERT INTO temp values (cur.owner, cur.name, cur.type);  END LOOP;  dbms_output.put_line('After CURSOR: ' || systimestamp);  COMMIT;  END;  /   CREATE OR REPLACE PROCEDURE BULK_COLLECT_QUERY  IS  TYPE sOwner IS TABLE OF VARCHAR2(30);  TYPE sName IS TABLE OF VARCHAR2(30);  TYPE sType IS TABLE OF VARCHAR2(19);  l_sOwner sOwner;  l_sName sName;  l_sType sType;  BEGIN  dbms_output.put_line('Before Bulk Collect: ' || systimestamp);  SELECT owner, object_name, object_type  BULK COLLECT INTO l_sOwner, l_sName, l_sType  FROM all_objects;  dbms_output.put_line('After Bulk Collect: ' || systimestamp);  --  FORALL indx IN l_sName.FIRST..l_sName.LAST  INSERT INTO temp values (l_sOwner(indx), l_sName(indx), l_sType(indx));  --  dbms_output.put_line('After FORALL: ' || systimestamp);  COMMIT;  END;  /

Ok, then I bounced the database to get no buffers, caching, etc. on it.

So the first execute

SQL> exec cursor_for_open_query
Before CURSOR OPEN: 27-SEP-07 10.56.30.699401000 AM +02:00
Before LOOP: 27-SEP-07 10.56.30.922366000 AM +02:00
After CURSOR CLOSE: 27-SEP-07 10.57.07.699791000 AM +02:00

Only look at the seconds it took 37 seconds and nearly nothing for opening the cursor! But how much rows were inserted?

SQL> select count(*) from temp;

COUNT(*)
———-
49424

Truncate the table (truncate to free the extends!) and bounce the database again and now the second run

SQL> exec cursor_for_query
Before CURSOR: 27-SEP-07 10.59.47.848249000 AM +02:00
After CURSOR: 27-SEP-07 11.00.09.072525000 AM +02:00

The whole loop took 22 seconds, well this looks already better. Well, also all rows inserted?

SQL> select count(*) from temp;

COUNT(*)
———-
49424

But now (after truncate and bouncing) the bulk collect run

SQL> exec bulk_collect_query
Before Bulk Collect: 27-SEP-07 11.01.33.553224000 AM +02:00
After Bulk Collect: 27-SEP-07 11.01.41.874054000 AM +02:00
After FORALL: 27-SEP-07 11.01.42.065753000 AM +02:00

Look at this, for bulking all the lines into the collection took just 8 seconds (for 49 424 rows) and the inserts just 1 second! Unbelievable, together we did everything in 9 seconds where the other ways took over 20 seconds!

Well now lets try to first execute the bulk load then truncate the table again but not bouncing the database so that the buffers and caches a still filled

SQL> exec bulk_collect_query
Before Bulk Collect: 27-SEP-07 11.02.31.257498000 AM +02:00
After Bulk Collect: 27-SEP-07 11.02.41.614205000 AM +02:00
After FORALL: 27-SEP-07 11.02.41.818092000 AM +02:00

PL/SQL procedure successfully completed.

SQL> select count(*) from temp;

COUNT(*)
———-
49423

SQL> truncate table temp;

Table truncated.

SQL> exec cursor_for_query
Before CURSOR: 27-SEP-07 11.04.04.960254000 AM +02:00
After CURSOR: 27-SEP-07 11.04.25.749038000 AM +02:00

Ok so now we need 10 seconds for the run with the bulk but we sill need 21 seconds for the cursor! So not really a improvement with the cache and so on. Ok final test on a big system with over 268 thousand rows

Before Bulk Collect: 27-SEP-07 11.24.17.034732000 AM +02:00
After Bulk Collect: 27-SEP-07 11.24.25.111020000 AM +02:00
After FORALL: 27-SEP-07 11.24.26.129826000 AM +02:00
PL/SQL procedure successfully completed.

COUNT(*)
———-
267985

Table truncated.

Before CURSOR: 27-SEP-07 11.24.29.629354000 AM +02:00
After CURSOR: 27-SEP-07 11.25.02.244549000 AM +02:00
PL/SQL procedure successfully completed.

COUNT(*)
———-
268056
And again, bulking took 8 seconds and the inserts just 1 second! But the run with the cursor took 33 seconds!

So this was just a short test but it definitely shows that BULK COLLECT and FORALL are much faster than cursors within the FOR loop! Only disadvantage of FORALL as you maybe already guess if you looked at the code: You can just perform one DML statement, there is no “FORALL END” clause! But anyway also bulking is a very high-performance functionality of Oracle! So if you have to run throw data collections then use BULK COLLECT!