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

Billy Verreynne <vslabs_at_onwe.co.za> wrote in message news:<b1vgnr$fa3$1_at_ctb-nnrp2.saix.net>...
> 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;
>

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

Append hint also prevents REDO and UNDO generation (unless you have bitmap indexes on a table). And write operations are expensive, so append hint can speed things up. Another thing is that you'll have to commit after each loop iteration if you use append hint. So it will slow things down. Use append hint but try to avoid PL/SQL loops and frequent commits. Rewrite as a SQL statement.

>
> 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...
>
> 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 turn logging off and on even after table creation (it is Oracle 8i, right ?). Use "alter table table_name nologging" . And yes, you want your table in nologging mode in order to take advantage of APPEND hint no redo/no undo feature.

Don't forget "alter session enable parallel dml" to use parallel insert.

HTH, Igor Izvekov. Received on Fri Feb 07 2003 - 11:10:52 CST