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

contrapositive wrote:

> 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;
By nature that will be slower as you're doing row-by-row processing. It is possible to perform the row processing done in the loop via SQL instead? If so then you can replace the loop with a single: INSERT INTO tblBIG
  SELECT blah FROM foo;
COMMIT; Where "blah" is the various decodes, conversions, maths, aggregations or whatever you need to do. SQL is pretty powerful - in my experience it is the exception to use LOOPs and cursors. The large majority of the time when dealing with data processing (i.e. ploughing through tons of data to create more data), that can be done by SQL without having to resort to row-by-row processing.

Wrt to the APPEND hint. From the manual: When you use the APPEND hint for INSERT, data is simply appended to a table. Existing free space in the blocks currently allocated to the table is not used.

Thus, all that you will be saving in performance is the check to re-use free space in the table. I'm not sure that this will provide any significant or noticeable performance increase.

What would make a big difference is parallel DML. If you can run the SELECT part of the INSERT in parallel and then use parallel DML to perform the inserting...

> 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.

What is taking the time in the loop? It's not the insert. An insert itself is pretty fast. What takes processing time is the SELECT and the rest of the code in that LOOP.

Thus you need to eliminate redundant and useless code in that loop. You need to make that SELECT as fast as possible.

Brings me back to my first suggestion - do not use a loop at all if possible.

Remember that for *every* insert into that table, you run a SELECT query. 3 million rows in the table at the end? 3 million loop iterations, executing the content of that loop, running that SELECT statement.

> 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?

No. Try it. See if there's a speed improvement. But I doubt that you will see it as that is negated by all the rest of the stuff that needs to be executed in each loop iteration.

> 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?

Ah.. interesting point. Yes, you can "turn of logging" (aka reduce the transaction overheads). But then you need to do a CREATE TABLE and not an INSERT. Something like:

CREATE TABLE bigTable
TABLESPACE user_data
STORAGE( ..whatever.. )
NOLOGGING AS
SELECT blah FROM foo

You can also make the SQL a parallel query if its suited for parallelisation.

--
Billy