Re: When to use bulk binds?

Would be nice if you can post your codes, easier to understand your situation that way. You use bulk bind to reduce the context switch overhead between SQL and PL/SQL. If you find yourself using collections in your codes and have to loop through the collections to process the data, use bulk bind. I would take a closer look to see if bulk bind can be used whenever you are using cursors and you are looping through the cursors to perform certain tasks.

Bulk bind may not always help, however. You don't want to create collections and use bulk bind when the task can be handled with a single, corectly written SQL. Don't use procedural codes when you can do it with a single SQL, as Tom Kyte always says.

• Jusung Yang

rgaffuri_at_cox.net (Ryan Gaffuri) wrote in message news:<1efdad5b.0305011003.40fed1f3_at_posting.google.com>...
> We have a batch process that does a long series of updates, inserts,
> and deletes. Right now we are using a script to execute sql.
>
> When is it appropriate to move this to PL/SQL and use array
> processing? couple of things I thought of...
>
> 1. We have one large table that has a column with 5-6 distinct values.
> We are often doing
>
> select stuff
> from big_table
> where column = 'BLAH'
>
> Would it be a good idea to bulk collect the rowids or even just select
> the entire row into a pl/sql table? and essentially break the table up
> into 5 smaller tables?
>
> then do a forall statement to do my update?
>
> In general when should you move from update, insert,delete statements
> with sub-queries to array processing? anyone have any rules of thumb?
>
> Here is an example explain plan... of one that takes 16 minutes.
>
> Execution Plan
> ----------------------------------------------------------
> 0 INSERT STATEMENT Optimizer=CHOOSE (Cost=17990 Card=1951628 B
> ytes=31226048)
>
> 1 0 SEQUENCE OF 'COL1'
> 2 1 FILTER
> 3 2 HASH JOIN (OUTER)
> 4 3 TABLE ACCESS (FULL) OF 'TAB1' (Cost=12345
> Card=195162
> 8 Bytes=13661396)
>
> 5 3 INDEX (FAST FULL SCAN) OF 'TAB2' (UNIQUE) (Cost=629
> Card=1673282 Bytes=15059538)
>
>
> Here is the query
>
> SELECT col1.nextval,
> 2,
> s.col2,
> 2,
> s.col3,
> NULL
> FROM tab1 ,
> tab2
> where tab1.col2 = tab2.col3 (+)
> and tab2.col3 is null
> AND tab2.col2 <> 'SA'
>
>
>
>
>
> Statistics
> ----------------------------------------------------------
> 8 recursive calls
> 8 db block gets
> 134918 consistent gets
> 132162 physical reads
> 0 redo size
> 460 bytes sent via SQL*Net to client
> 903 bytes received via SQL*Net from client
> 3 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 0 rows processed
Received on Thu May 01 2003 - 18:34:19 CDT