Should I use an /*+ append */ hint here???

Hi. I have an INSERT INTO statement that runs quite often. Actually it sits inside a loop:

(pseudo-code:)
LOOP
  INSERT INTO tblBIG
  SELECT /*+ ordered */ a, b, c, ... FROM tblA, tblB, tblC, ...

END LOOP; So tblBIG is usually empty when the loop (procedure) begins, and could have up to two million rows when it's done. Each INSERT puts anywhere from 0 to 3000 rows in the table.

tblBIG is indexed; it has a unique index on two fields called ListNo and ItemNo. The loop iterates through list numbers and the query brings back all items associated with that list.

After the program is run, some other process imports the data from tblBIG and deletes all the records.

So we're trying to optimize this. I'm wondering if an /*+ append */ hint will speed things up. Is there any danger to using it? Also, if there's data loss, it's not a big deal (the data can be reconstructed pretty easily), so is there some logging feature we can turn off for this table? And how do I describe it to our DBA who says there isn't?

Anything else come to mind? (I know there are probably better ways to do this (SQL*Loader perhaps) but this is what we're stuck with for now, so I'm really just looking for some tuning hints.)

Thanks in advance.

-jk Received on Thu Feb 06 2003 - 18:59:05 CST