FORALL and STATEMENT level trigger

Just an addendum on one of the points in my previous post.

In that post, I pointed out, amongst other things, that for a FORALL .. INSERT statement, the statement level trigger fired only once (basic evidence in previous post).

This was something which surprised me.

Just from further observations:

• FORALL INSERT – Single statement – i.e. Statement trigger fires once.
• FORALL UPDATE – Multiple statements – i.e. Statement trigger fires multiple times.
• FORALL DELETE – Multiple statements – i.e. Statement trigger fires multiple times.

Which kind of backs up my original assertion in the forum thread, which was that when working with a collection and using statement level trigger work, a single INSERT/UPDATE/DELETE using TABLE (CAST ( )) should be preferable to FORALL.

The constant requirement to re-review the basics, previously known “facts”, knowledge gaps and also your assumptions is one of the things which makes working with Oracle so interesting.

SQL> SET serveroutput ON  
SQL> 
SQL> CREATE TABLE test_bulk  
  2  (test_id    NUMBER  
  3  , id_test_1  NUMBER  
  4  , id_test_2  NUMBER  
  5  ); 

Table created.

SQL> CREATE OR REPLACE TRIGGER test_asi  
  2  AFTER INSERT OR UPDATE OR DELETE
  3  ON TEST_BULK  
  4  REFERENCING NEW AS New OLD AS Old  
  5  DECLARE  
  6  begin  
  7     DBMS_OUTPUT.PUT_LINE('expensive action');  
  8  end;  
  9  /

Trigger created.

SQL> SELECT COUNT(*) FROM test_bulk;

  COUNT(*)
----------
         0

SQL> DECLARE  
  2    TYPE my_collection IS TABLE OF NUMBER;  
  3    my_collection_test  my_collection;  
  4  BEGIN  
  5     SELECT LEVEL BULK COLLECT  
  6     INTO   my_collection_test  
  7     FROM   dual  
  8     CONNECT BY LEVEL < 15;  
  9     FORALL i IN 1 .. my_collection_test.COUNT  
 10       INSERT INTO test_bulk(id_test_1,id_test_2) VALUES (1,my_collection_test(i));  
 11  END;  
 12  /  
expensive action

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM test_bulk; 

  COUNT(*)
----------
        14

SQL> DECLARE  
  2    TYPE my_collection IS TABLE OF NUMBER;  
  3    my_collection_test  my_collection;  
  4  BEGIN  
  5     SELECT LEVEL BULK COLLECT  
  6     INTO   my_collection_test  
  7     FROM   dual  
  8     CONNECT BY LEVEL < 15;  
  9     FORALL i IN 1 .. my_collection_test.COUNT  
 10       UPDATE test_bulk
 11       SET    id_test_1 = 1
 12       ,      id_test_2 = my_collection_test(i)
 13      WHERE id_test_2 = my_collection_test(i);  
 14  END;  
 14  / 
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action

PL/SQL procedure successfully completed.

SQL> DECLARE  
  2    TYPE my_collection IS TABLE OF NUMBER;  
  3    my_collection_test  my_collection;  
  4    my_collection_test2  my_collection;  
  5  BEGIN  
  6     SELECT LEVEL BULK COLLECT  
  7     INTO   my_collection_test  
  8     FROM   dual  
  9     CONNECT BY LEVEL < 15;  
 10     FORALL i IN 1 .. my_collection_test.COUNT  
 11       DELETE test_bulk
 12       WHERE  id_test_2 = my_collection_test(i)
 13       RETURNING id_test_2 BULK COLLECT INTO my_collection_test2;  
 14  END;  
 15  /  
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM test_bulk;

  COUNT(*)
----------
         0

SQL> 

Upscaling your JDBC app using Oracle object type collection

Regarding oracle jdbc bulk array inserts, Greg Rahn wrote this week about the performance gains to be had by batching up calls to the database using the array interface.

As an addendum to his excellent points, please find attached a comparison with using an Oracle collection of an Oracle object type – forgive my Java. Using StructDescriptor, STRUCT, ArrayDescriptor and ARRAY structures is unsightly and unintuitive but they can deliver some further performance gains. If only we could wrap this approach up in some user-friendly layer then I reckon we could kick some of these iterative row-by-row ORM tools into touch.

First up, for the baseline, based On Greg’s example, this is what my batch size performance was like inserting 10000 rows into emp on my system:

And, using an Oracle collection of Oracle object types, uploading the 10000 rows in a single INSERT… TABLE … CAST statement it took 0.219 seconds – Java class here.

Which compared very favourably.

Inline scripts:

create type to_emp as object
(EMPNO NUMBER(4)
,ENAME VARCHAR2(10)
,JOB VARCHAR2(9)
,MGR NUMBER(4)
,HIREDATE DATE
,SAL NUMBER(7,2)
,COMM NUMBER(7,2)
,DEPTNO NUMBER(2));
/

create type tt_emp as table of to_emp;
/

import java.sql.*;
import java.util.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
import oracle.sql.ArrayDescriptor;
import oracle.sql.ARRAY;


public class bulkInsert {

    public static void main(String[] args) {
        try {

            OracleDataSource ods = new OracleDataSource();
            ods.setURL("jdbc:oracle:oci8:@ora.noldb507");
            ods.setUser("scott");
            ods.setPassword("tiger");
            OracleConnection conn = (OracleConnection) ods.getConnection();
            conn.setAutoCommit(false);

            short seqnum = 0;
            String[] metric = new
                    String[OracleConnection.END_TO_END_STATE_INDEX_MAX];

            metric[OracleConnection.END_TO_END_ACTION_INDEX] = "insertEmp";
            metric[OracleConnection.END_TO_END_MODULE_INDEX] = "bulkInsert";
            metric[OracleConnection.END_TO_END_CLIENTID_INDEX] = "myClientId";
            conn.setEndToEndMetrics(metric, seqnum);

            DatabaseMetaData meta = conn.getMetaData();

            System.out.println(
                    "JDBC driver version is " + meta.getDriverVersion());

            Statement stmt = conn.createStatement();

            stmt.execute("alter session set sql_trace=true");
            stmt.execute("truncate table emp");

			int numberOfEmployees = Integer.parseInt(args[0]);

            STRUCT[] structEmployee = new STRUCT[numberOfEmployees];

            oracle.sql.StructDescriptor descEmployee = oracle.sql.StructDescriptor.createDescriptor("SCOTT.TO_EMP",conn);

            java.sql.Timestamp now = new java.sql.Timestamp( (new java.util.Date() ).getTime() );

            for (int i = 0; i < numberOfEmployees; i++) {

                Object[] empValues = {
                  (i), // EMPNO
                  ("Name" + i), // ENAME
                  ("Job"), // JOB
                  (i), // MGR
                  now , //now
                  (10000 + i), // SAL
                  (100 + i), // COMM
                  (10) // DEPTNO
                };

                structEmployee[i] = new oracle.sql.STRUCT(descEmployee, conn, empValues);
            }

            oracle.sql.ArrayDescriptor empArrayDescriptor = oracle.sql.ArrayDescriptor.createDescriptor("SCOTT.TT_EMP",conn);

            ARRAY empArray = new ARRAY(empArrayDescriptor,conn,structEmployee);

            OraclePreparedStatement psEmp = (OraclePreparedStatement) conn.prepareStatement(
             "insert /* insEmpBulk */ into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) select * from table (cast (? as tt_emp))");

            psEmp.setObject(1,empArray);

            long start1 = System.currentTimeMillis();

            // Set the batch size for each statment
            ((OraclePreparedStatement) psEmp).execute();

            conn.commit();
            psEmp.close();

            long elapsedTimeMillis1 = System.currentTimeMillis() - start1;
            // Get elapsed time in seconds
            float elapsedTimeSec1 = elapsedTimeMillis1 / 1000F;

            System.out.println("elapsed seconds: " + elapsedTimeSec1);

            conn.close();

        } catch (Exception e) {
            System.err.println("Got an exception! ");
            System.err.println(e.toString());
        }
    }
}

ORM

Aaaaarggghh. 

ORM – Object Relational Mapping – seems to be the bane of a lot of Oracle Developers / DBAs these days.

 I’m not talking about great Oracle features such as Oracle relational views using UDTs (User-Defined Types) and operators such as CAST, TABLE and MULTISET.

More the object/relational persistence layer type things like Hibernate, iBATIS, JDO, SDO, etc.

I get the whole thing about it saving application developers from writing so much code and therefore there is a reduction in testing and errors, and the application gets written that much quicker (in theory), etc.

But often it’s like death by one thousand little generated SQL statements where one bigger one could do the job much more efficiently.  Either that or you find the most horrific query you’ve ever seen has been generated with seemingly hundreds of tables, silly aliases, and hoardes of UNIONs and ORs, etc.

Maybe one of the problems has been that the DAO layer has never been particularly cool or trendy and that most application developers have never been into writing SQL – it’s always been a bit of a chore and boring to them. But SQL isn’t difficult. You’ve just got to think in sets.

And I’m sure that this one of those scenarios where the oft-quoted 80:20 “rule” can be applied – i.e that an ORM tool might make sense 80% of the time, particularly when SQL experts aren’t available. Trouble is that you can turn that silly rule right around and say that the 20% of code where these ORMs don’t work very well take up 80% of the effort.

The problem for me with this is the database becomes more like just a bucket. And a bucket which is accessed by inefficient SQL. The database was born to store data and manipulate data using set operations. More often than not with ORM, we find row-by-row operations, we don’t see efficient set operations, we don’t see bulk operations, we see dynamically generated IN lists, we see lots of OR clauses, etc.

And then, more often that not, when there’s a problem with that SQL, there’s nothing that can be done about it.

Going back to the O-R features in Oracle, these have been steadily developed since 8i. I’m a big fan of creating a layer of O-R views to create more appropriate interfaces for the application to the database entities and have used them to great success in a varietyof commercial JDBC applications. And it always comes as a surprise to the application developers that it is possible to pass Oracle UDT collections back and forward. Granted, the JDBC is a little fiddly, but it’s a good way of doing efficient set/bulk operations on entities that are a little more natural to the OO world than the base relational entities. It’s a pity that ODP.NET does not yet have the same support for these Oracle Types.

Maybe one day all the application developers or (80%) will be replaced by code generators that work from a few boxes and a few lines put together on something like a Visio diagram. I hope not because I consider myself an application developer/designer starting from the database going out to and including the application.

Alternatively, maybe boxes, memory and disk space get so big and fast that these inefficiencies aren’t a concern anymore (either that or the affects of the inefficiencies are magnified).

I want to bulk up II

Here’s what I was talking about
Just quickly, why does this use EXECUTE IMMEDIATE to sum up the latches? Because this was done in a strictly controlled banking environment and lots of people don’t have access to the V$ views. So, if they can’t access the v$latch view, the package still works albeit just providing a crude clock speed comparison.

DROP VIEW my_test_view;
DROP TABLE my_test_table;
DROP TYPE tt_my_test_type;
DROP TYPE to_my_test_type;

CREATE OR REPLACE TYPE to_my_test_type AS OBJECT
(col1 NUMBER
,col2 NUMBER
,col3 NUMBER
,col4 NUMBER
,col5 NUMBER);
/

CREATE OR REPLACE TYPE tt_my_test_type
AS TABLE OF to_my_test_type;
/CREATE TABLE my_test_table
(col1 NUMBER
,col2 NUMBER
,col3 NUMBER
,col4 NUMBER
,col5 NUMBER);

CREATE OR REPLACE VIEW my_test_view
AS
SELECT to_my_test_type(
col1
, col2
, col3
, col4
, col5) my_row
FROM my_test_table;

CREATE OR REPLACE PACKAGE my_test_package
AS
--
PROCEDURE my_test (
i_num_rows IN NUMBER DEFAULT 100
);
--
END;
/

CREATE OR REPLACE PACKAGE BODY my_test_package
AS
--
e_no_latch_access EXCEPTION ;
PRAGMA EXCEPTION_INIT (e_no_latch_access,-942);
--
TYPE to_test IS RECORD
(testname VARCHAR2(100)
,timetaken NUMBER
,sumlatches NUMBER
,cnt NUMBER);
TYPE tt_test IS TABLE OF to_test INDEX BY PLS_INTEGER;
--
g_test tt_test;
g_rows NUMBER;
--
PROCEDURE p_start (
i_testname IN VARCHAR2
)
AS
v_ind NUMBER;
BEGIN
v_ind := NVL(g_test.LAST,0)+1;
g_test(v_ind).testname := i_testname;
g_test(v_ind).timetaken := DBMS_UTILITY.GET_TIME;
BEGIN
EXECUTE IMMEDIATE 'SELECT SUM(gets) FROM v$latch' INTO g_test(v_ind).sumlatches ;
EXCEPTION WHEN e_no_latch_access THEN g_test(v_ind).sumlatches := 0;
END;
END p_start;
--
PROCEDURE p_end
AS
v_latches NUMBER;
BEGIN
--
BEGIN
EXECUTE IMMEDIATE 'SELECT SUM(gets) FROM v$latch' INTO v_latches;
EXCEPTION WHEN e_no_latch_access THEN v_latches := 0;
END;
g_test(g_test.LAST).timetaken := DBMS_UTILITY.GET_TIME - g_test(g_test.LAST).timetaken;
g_test(g_test.LAST).sumlatches := v_latches - g_test(g_test.LAST).sumlatches;
SELECT count(*) INTO g_test(g_test.LAST).cnt FROM my_test_table;
END p_end;
--
PROCEDURE p_initialise
AS
BEGIN
DELETE FROM my_test_table;
g_test.DELETE;
p_start('Start');
p_end;
END p_initialise;
--
PROCEDURE p_report
AS
BEGIN
FOR i IN g_test.FIRST .. g_test.LAST
LOOP
IF g_test(i).testname != 'Start'
THEN
DBMS_OUTPUT.PUT_LINE('=======================');
DBMS_OUTPUT.PUT_LINE('Proc: '||g_test(i).testname);
DBMS_OUTPUT.PUT_LINE('Time: '||to_char(g_test(i).timetaken,'99999')||' hsecs; Latches: '||to_char(g_test(i).sumlatches,'9999999999999'));
DBMS_OUTPUT.PUT_LINE('Count: '||g_test(i).cnt);
END IF;
END LOOP;
END p_report;
--
PROCEDURE my_test_straight_sql
AS
BEGIN
--
p_start('my_test_straight_sql');
--
INSERT INTO my_test_table
SELECT ROWNUM
, ROWNUM+1
, ROWNUM+2
, ROWNUM+3
, ROWNUM+4
FROM DUAL
CONNECT BY ROWNUM < g_rows;
--
p_end;
--
END my_test_straight_sql;
--
PROCEDURE my_test_object_view1
AS
BEGIN
--
p_start('my_test_object_view1');
--
INSERT INTO my_test_view
SELECT to_my_test_type(
ROWNUM
, ROWNUM+1
, ROWNUM+2
, ROWNUM+3
, ROWNUM+4)
FROM DUAL
CONNECT BY ROWNUM < g_rows;
--
p_end;
--
END my_test_object_view1;
--
PROCEDURE my_test_object_view2
AS
CURSOR my_cursor
IS
SELECT to_my_test_type(
ROWNUM
, ROWNUM+1
, ROWNUM+2
, ROWNUM+3
, ROWNUM+4)
FROM DUAL
CONNECT BY ROWNUM < g_rows;
--
my_array tt_my_test_type;
--
BEGIN
--
p_start('my_test_object_view2');
--
OPEN my_cursor;
FETCH my_cursor BULK COLLECT INTO my_array;
--
INSERT INTO my_test_view
SELECT VALUE(t)
FROM TABLE( CAST (my_array AS tt_my_test_type)) t;
--
p_end;
--
END my_test_object_view2;
--
PROCEDURE my_test_object_view3
AS
CURSOR my_cursor
IS
SELECT to_my_test_type(
ROWNUM
, ROWNUM+1
, ROWNUM+2
, ROWNUM+3
, ROWNUM+4)
FROM DUAL
CONNECT BY ROWNUM < g_rows;
--
my_array tt_my_test_type;
--
BEGIN
--
p_start('my_test_object_view3');
--
OPEN my_cursor;
FETCH my_cursor BULK COLLECT INTO my_array;
--
FORALL i IN my_array.FIRST .. my_array.LAST
INSERT INTO my_test_view
VALUES (my_array(i));
--
p_end;
--
END my_test_object_view3;
--
PROCEDURE my_test_forall
AS
--
CURSOR my_cursor
IS
SELECT ROWNUM
, ROWNUM+1
, ROWNUM+2
, ROWNUM+3
, ROWNUM+4
FROM DUAL
CONNECT BY ROWNUM < g_rows;
--
TYPE t_col1 IS TABLE OF my_test_table.col1%TYPE;
TYPE t_col2 IS TABLE OF my_test_table.col1%TYPE;
TYPE t_col3 IS TABLE OF my_test_table.col1%TYPE;
TYPE t_col4 IS TABLE OF my_test_table.col1%TYPE;
TYPE t_col5 IS TABLE OF my_test_table.col1%TYPE;
v_col1 t_col1;
v_col2 t_col2;
v_col3 t_col3;
v_col4 t_col4;
v_col5 t_col5;
BEGIN
--
p_start('my_test_forall');
--
OPEN my_cursor;
FETCH my_cursor BULK COLLECT INTO
v_col1,
v_col2,
v_col3,
v_col4,
v_col5;
--
FORALL i IN v_col1.FIRST .. v_col1.LAST
INSERT INTO my_test_table
VALUES (
v_col1(i),
v_col2(i),
v_col3(i),
v_col4(i),
v_col5(i)
);
--
p_end;
--
END my_test_forall;
--
PROCEDURE my_test_iterator
AS
--
-- inline view because otherwise it doesn't work in version
CURSOR my_cursor
IS
SELECT *
FROM (
SELECT ROWNUM col1
, ROWNUM+1 col2
, ROWNUM+2 col3
, ROWNUM+3 col4
, ROWNUM+4 col5
FROM DUAL
CONNECT BY ROWNUM < g_rows);
--
my_rec my_cursor%ROWTYPE;
--
BEGIN
--
p_start('my_test_iterator');
--
OPEN my_cursor;
LOOP
FETCH my_cursor INTO my_rec;
EXIT WHEN my_cursor%NOTFOUND;
--
INSERT INTO my_test_table
VALUES (
my_rec.col1,
my_rec.col2,
my_rec.col3,
my_rec.col4,
my_rec.col5
);
--
END LOOP;
--
p_end;
--
END my_test_iterator;
--
PROCEDURE my_test (
i_num_rows IN NUMBER DEFAULT 100
)
AS
BEGIN
--
g_rows := NVL(ABS(i_num_rows),0)+1;
--
p_initialise;
my_test_straight_sql;
my_test_object_view1;
my_test_object_view2;
my_test_object_view3;
my_test_forall;
my_test_iterator;
p_report;
--
END my_test;
--
END my_test_package;
/

set serveroutput on

BEGIN
my_test_package.my_test(10000);
END;
/

I want to bulk up I

A recent query on a private forum at work had a fellow working on a piece of code with a cursor fetching rows from a table and then a for loop inserting those rows into a table.

His Oracle Architect had decreed that this should be improved to use bulk processing. The poster had changed the cursor to do a bulk collect into a table variable which was of the type cursor%ROWTYPE.

And then he was doing a FORALL insert into another destination table and was running into the old “PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records.”

This is a frustrating limitation. I wonder why Oracle decided to implement this functionality in this way. It doesn’t seem an unreasonable thing to want to do.

One obvious alternative was to fetch into multiple single dimension arrays – i.e. one array per column fetched. The downside being that some 35 columns were being selected which makes for wordy code.

Another alternative was to go through the rigmarole, usually my preferred rigmarole, of creating a sql object type, a sql table type, bulk collecting the cursor into an array and then just using sql with the TABLE and CAST functionality.

At this point, I thought I should just double check the facts. Why was it necessary to do open a CURSOR, FETCH using BULK COLLECT, close and then do a FORALL insert? Would not a simple “INSERT … SELECT” work?

Well, apparently it would work. But the poster was reluctant to rework the original beyond a certain extent. Besides, he says, “INSERT … SELECT” is not a bulk operation and that’s what his Oracle Architect had decreed preferable.

“Insert … select” is the original bulk operations. Maybe it’s more reassuring that you can see the progressive development on a piece of code through code control and see how it has developed (craziness more like). In other words, a manager could look at the code and see that the words BULK COLLECT had been added therefore job done.

Anyway, I put together a simple demo script to demonstrate some of the options to the poster.

One of the interesting things that came out of the demo was that the SELECT being used to generate the data had some interesting behaviour. This rang a distant bell. I think I remember reading about this caveat on the website where I originally read about the ability to CONNECT BY ROWNUM.

In Oracle XE:

SQL> l
  1 select rownum
  2 from dual
  3* connect by rownum < 5
SQL> /

ROWNUM
----------
  1
  2
  3
  4

In 9.2.0.6:

SQL> l
  1 select rownum
  2 from dual
  3* connect by rownum < 5
SQL> /


ROWNUM
----------
  1


But:


SQL> l
  1 select *
  2 from (
  3 select rownum
  4 from dual
  5 connect by rownum < 5
  6* )
SQL> /



ROWNUM
----------
  1
  2
  3
  4

This oddity apart, it’s a cool little piece of functionality.Anyway, in case your interested, what follows is the demo in full.