I've moved...

This blog now has a new home - please update your shortcuts and readers to: www.jeffkemponoracle.com. Sorry for any inconvenience!

Saturday, March 18, 2006

"BULK COLLECT and FORALL are faster than cursor processing"

Seen in the wild:
SELECT ROWID
BULK COLLECT INTO t_rowids
FROM   my_table
WHERE  ...
FOR UPDATE NOWAIT;

IF t_rowids.COUNT > 0 THEN
FORALL i IN t_rowids.FIRST..t_rowids.LAST
DELETE FROM my_table
WHERE ROWID = t_rowids(i);
END IF;

[Full Article]

7 comments:

  1. I have this java program which needs to do a bulk update?

    How could I compile your code into my program?

    Is it best to just use System command and execute the PL/SQL?

    Thanks,
    Laurie

    ReplyDelete
  2. Hi laurie29,

    (I hope you realise that the code sample was given as an illustration of what NOT to do :) )

    I assume you are just wanting to know how to do bulk updates in general.

    I'm not very experienced with Java - but I do know that you can run arbitrary SQL within a Java program, including bulk updates.

    You could start with the Oracle Java Developer's Guide,
    or the Oracle JDBC Developer's Guide, however I don't know where exactly in these documents the answer to your question is.

    This section may help you.

    Alternatively I would suggest that the bulk update be implemented in a PL/SQL procedure or package, then called from Java.

    Thanks for the question! I hope this helps.

    ReplyDelete
  3. can BULK COLLECT be used in Oracle forms?

    ReplyDelete
  4. Hi thecraic,

    I believe whether you can use the new PL/SQL syntax in Oracle Forms depends on the version - e.g. Forms 6i does not support BULK COLLECT.

    Anyway, a good practice generally is to implement DML like this in a database package; that way you can use the latest features, even if Forms doesn't support them.

    ReplyDelete
  5. hi jeffery
    To use bulk collect and forall does
    the target and source table have to have the exact same structure ?
    What about when loading a target table from a staging table.
    The targe table has a PK that is updated from a trigger but the staging does not have this column.
    How do you get around that ?

    Thanks,

    Dan

    ReplyDelete
  6. Hi Dan,

    This is a "quick and dirty" answer. For more detailed help please follow up with more details about what you're trying to do. Briefly:

    Bulk collect requires the pl/sql table you're using to have the same structure as the query you're selecting from.

    Performing DML with a FORALL statement does not require any particular structure; the pl/sql table simply provides values which you can reference in the insert/update/delete statement.

    Sure, you can use bulk collect and forall to load data from a staging table to a target table; however, often you can skip that step and just do a direct "insert as select" to transfer the data.

    In your last question you say your target table has a PK which is supplied by a trigger. If you're inserting into a table like this, simply omit the PK column from your insert statement.

    I hope this helps. I might have misunderstood your question so feel free to follow up with more details about what you're trying to accomplish.

    Cheers!

    ReplyDelete
  7. I Kemp
    do you have any answer on this please advice

    http://forums.oracle.com/forums/thread.jspa?threadID=622794&tstart=0

    ReplyDelete

Note: only a member of this blog may post a comment.