Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Array Inserts
>>>>> "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