Thursday, June 5, 2008

BULK COLLECT and FORALL

I have a task to tune a little bit of SQL. It's very ETL like, but the target is not (yet) a star schema, it's pretty much a table flattened out for reporting purposes.

As I reviewed it, I noticed it went row by row, with a COMMIT inside the LOOP. That's gotta go.

Can I do this in one SQL statement? No, there's other processing that needs to be done (UPDATE two other tables before and after). Hmmm...could I just return the appropriate records into a collection? I'll have to look at that to see if it's possible.

For now though, I am going to try and use BULK COLLECT with the LIMIT clause and FORALL for the processes that occur before and after.

Let's create some data:

CREATE TABLE t( x NUMBER, y NUMBER );

INSERT INTO t( x, y )
SELECT
TRUNC( dbms_random.value( 1, 99999999 ) ),
TRUNC( dbms_random.value( 1, 100000 ) )
FROM dual
CONNECT BY level < 1001;
OK, now let's create an anonymous block, BULK COLLECTing the data from T into a PL/SQL table and then populated another table with that data:

DECLARE
TYPE t_record IS TABLE OF T%ROWTYPE;
l_table T_RECORD;
CURSOR c
IS
SELECT x, y
FROM t;
BEGIN
OPEN c;

LOOP
FETCH c
BULK COLLECT INTO l_table
LIMIT 100;

FORALL i IN 1..l_table.COUNT
INSERT INTO s ( x, y )
VALUES ( l_table(i).x, l_table(i).y );

EXIT WHEN C%NOTFOUND;
END LOOP;
CLOSE c;
END;
/
And then I run it and I get the following:

ERROR at line 18:
ORA-06550: line 18, column 16:
PLS-00436: implementation restriction: cannot reference
fields of BULK In-BIND table of records
ORA-06550: line 18, column 16:
PLS-00382: expression is of wrong type
ORA-06550: line 18, column 30:
PLS-00436: implementation restriction: cannot reference
fields of BULK In-BIND table of records
ORA-06550: line 18, column 30:
PLS-00382: expression is of wrong type
ORA-06550: line 18, column 16:
PL/SQL: ORA-22806: not an object or REF
ORA-06550: line 17, column 7:
PL/SQL: SQL Statement ignored
A quick google search and I end up here .

So I can bulk bind, but I have to INSERT into the table as a whole. I can't be selective.

I updated my code to this:

DECLARE
TYPE t_record IS TABLE OF T%ROWTYPE;
l_table T_RECORD;
CURSOR c
IS
SELECT x, y
FROM t;
BEGIN
OPEN c;

LOOP
FETCH c
BULK COLLECT INTO l_table
LIMIT 100;

FORALL i IN 1..l_table.COUNT
INSERT INTO s
VALUES l_table(i);

EXIT WHEN C%NOTFOUND;
END LOOP;
CLOSE c;
END;
/
I run it and it completes successfully. This is all on XE, so I wonder (hope) that 11g will allow me to do what I want (I'll be working on an 11g RAC system). I scroll down the list of google results and I find this one which then takes me to AskTom. The first post demonstrates that my first attempt will work on 11g.

BULK COLLECT and FORALL are great tools if you can't do it in a single SQL statement and if you want to avoid the row by row processing.

9 comments:

Tom said...

Dude, you need to install 11g at home if you are working on 11g in the office :)...

Install 11g on your work machine too so you have something to test on. :)

Tim... said...

Hi.

11g has removed the restriction about referencing individual columns in a collection within a FORALL statement, so your original example will work fine in 11g.

Cheers

Tim...

DomBrooks said...

In 11g, you should find that that FORALL implementation restriction has been lifted, i.e. you can reference the fields.

DomBrooks said...

Also, note that other workarounds include using TREAT as illustrated by Adrian Billington.

Alistair Wall said...

If you want to update a subset of columns with bulk update, you can do it by defining a view.

Anonymous said...

Don`t use %NOTFOUND with BULK COLLECT LIMIT.

Have a look at:
http://www.oracle.com/technology/oramag/oracle/08-mar/o28plsql.html

for an explanation.

Jan

oraclenerd said...

@tom

I do have it installed on both, I just didn't have everything setup properly yet.

@tim
I did see that on the link, just didn't get around to trying it myself on 11g. I'm glad it was improved upon.

@dom

I've seen TREAT used only once before, but haven't used it myself. Maybe this is a good excuse?

Anonymous said...

Great post. Lots of info

Thanks
-Pradip

Vijay Kumar said...

Hi,
For example, Table T have 3 columns and
cursor is selecting 12 columns
then what how to use the FORALL statement in Insert Statement