RE: [sqr-users] Insert -SQR

["George Jansen" <GJANSEN@aflcio.org>]

1. A commit will close FOR UPDATE cursors. It does not affect other cursors.

2. You have not indicated--or at least I have missed--how large your insert is 
compared to the size of your table. If it is a large enough fraction, then, 
yes, you could save a lot of time by writing to a temporary table, disabling 
indexes, and putting

ALTER SESSION SKIP DISABLED INDEXES;

into a begin-sql paragraph, and adding an /*+ APPEND */ hint to your insert 
statement.

3. You really need to know where the time is going. Why not have your DBA help 
you set up tests as follows. Start with a table of records such as will be 
ultimately inserted into the target table.

a. Time the selection of rows from your source table. Does that finish in 
reasonable time?
b. Time a single-statement insert of all rows. Does that finish in reasonable 
time? If it does,
c. Time a row-by-row insertion, i.e
FOR tr IN (SELECT * FROM test_table) LOOP
  INSERT INTO target_table (a, b, c, ...)
  VALUES (tr.a, tr.b, tr.c, ...);
END LOOP;
/
Does that finish in reasonable time?

Oracle has tools to help you determine where the time goes. Look at 
www.miracleas.dk, look at www.hotsos.com for some pointers on using the wait 
interface to tune.



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users