Very Computer

We have a statement to the effect :

INSERT into tablename_1
(SELECT ..... from tablename_2)

We cannot use a cursor and commit every n records. The statement is
executed and results in 5 million rows being copied from tablename_1 to
tablename_2. As a result, we need very large rollback statements which
we want to avoid.

Is there some init parameter we can set when our database instance
starts up that would tell Oracle to perform a COMMIT say every 100,000
rows ?

Thanks.

- Doug
- Oracle certified DBA.

"If everything feels like it's under control, you're probably not
going fast enough" - Mario Andretti

Quote:>Hi all

>We have a statement to the effect :

>INSERT into tablename_1
>(SELECT ..... from tablename_2)

>We cannot use a cursor and commit every n records. The statement is
>executed and results in 5 million rows being copied from tablename_1 to
>tablename_2. As a result, we need very large rollback statements which
>we want to avoid.

>Is there some init parameter we can set when our database instance
>starts up that would tell Oracle to perform a COMMIT say every 100,000
>rows ?

>Thanks.

Depending on requirements, you could change the
table to a NOLOGGING table (alter table XXX nologging)
then do a direct mode insert:

insert /*+ append */ into XXX
select etc.

There are several restrictions though, which may
make this approach unsuitable.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
Book bound date: 8th Dec 2000
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html

Quote:> Hi all

> We have a statement to the effect :

> INSERT into tablename_1
> (SELECT ..... from tablename_2)

> We cannot use a cursor and commit every n records. The statement is
> executed and results in 5 million rows being copied from tablename_1
to
> tablename_2. As a result, we need very large rollback statements which
> we want to avoid.

> Is there some init parameter we can set when our database instance
> starts up that would tell Oracle to perform a COMMIT say every 100,000
> rows ?

> Thanks.

--
Michael Krolewski
Rosetta Inpharmatics

              Ususual disclaimers

Sent via Deja.com http://www.deja.com/
Before you buy.