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

As a follow up, many seem to think that I should ditch the loop and go with a single bulk insert:

INSERT INTO
SELECT /*+ ordered */ a, b, c, ... FROM tblA, tblB, tblC, ... WHERE ListNo IN (SELECT ListNo FROM tblLists) AND ...

Two problems I have with this (but I'm willing to hear workarounds): 1. There could be up to 2000 List No's, and the single INSERT would stuff two to three million rows in to the table at once. Can the database handle an operation of this magnitude? Will it really be faster? Roughly how much so?
2. For each ListNo that is dealt with, there is a slight chance of getting a DUP_VAL_ON_INDEX error -- that is, the ListNo already exists in the destination table. This error is handled explicitly by deleting all rows from the destination table for that ListNo and trying again. I'm not sure I could do that if I go with a bulk insert.

Also, I am commiting after each insert, but I suppose I could scale that back and commit after every n insert's. Maybe that'll help.

"contrapositive" <contrapositive_at_hotmail.com> wrote in message news:b1v0bj$16vvbf$1_at_ID-149469.news.dfncis.de...
> 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 Fri Feb 07 2003 - 16:32:24 CST