When to use bulk binds?

From: Ryan Gaffuri <rgaffuri_at_cox.net>
Date: 1 May 2003 11:03:12 -0700
Message-ID: <1efdad5b.0305011003.40fed1f3@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 - 13:03:12 CDT