Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Array Inserts

Re: Array Inserts

From: Andy Cowling <arc_at_lapwing.uk.sequent.com>
Date: 16 Jun 1999 09:58:00 +0100
Message-ID: <85n1y01q07.fsf@lapwing.uk.sequent.com>


>>>>> "Ming" == Ming <fungs_at_logica.com> writes:

    Ming> First of all, thanks for the response to my SQL*Loader
    Ming> question.  I've been told by certain parties that an Pro*C
    Ming> array insert maybe a viable alturnative using a direct
    Ming> load. The problem is that a number of indexes need to be
    Ming> rebuilt after a load.

Yes. I have used Pro*C array inserts as an alternative to direct path loaded. Pro*C was needed to perform data transformations - we could have used conventional SQL*Loader but for its poor performance on large volumes (in 7.3.x at least).

You can either drop the indexes and recreate (parallel, unrecoverable) or preserve them and take the performance hit.

    Ming> Has any one used the "direct-load" Insert ( not loader ) out     Ming> there, is the performance comparable to SQL*Loader.

I think you are referring to the parallel DML functionality introduced in Oracle 8. This is a neat feature and works very well - speed is certainly on a par with a CTAS (CREATE TABLE AS SELECT) but probably not comparable with a direct-path load and can use parallelism on both the SELECT and the INSERT.

You use it as follows :

    sqlplus very/secret

    alter session enable parallel dml;

    insert /*+ PARALLEL */ into <source> select * from <target>;

If you are obsessive about performance, you can also use the following to help things along.

    alter table <source> nologging;

HTH
--
Andy Cowling
Sequent Computer Systems, Weybridge, UK Received on Wed Jun 16 1999 - 03:58:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US