My weekly tip: massive insert and update

From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Mon, 15 Mar 2004 17:12:11 -0400
Hi I had to optimize massive insert and deletes, but the most interesting
and curuious I
found is, after optimizing a process using bulk inserts, this took slightly
more time,
but the statpacks reports, show this was using less cpu, etc, all was
optimal.

Looking at tom site, I found a similar situation, a update that took more
time, but it
was using less resources, and Tom Kyte showed it was better.
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6407993912330

It's curious, because I always thought that a tunned should always be
faster, but
it seems there are exceptions

Here is the text I got, I hope this could be helpfull to someone

1.1.1              Faster Updates
1)       If you are executing millions of updates a better option can be
create table2 as select … from table1;
drop table table1;
rename table2 to table1
add indexes and constraints;

You additionally use nologging tables(avoid redo generation) and append
hint(avoid undo generation), but you must be aware about the consequences
explained in “Faster Inserts”.

2)If you know several rows has the new value you want to set, do the
following

UPDATE TABLE SET COLUMN=’VALUE’ WHERE NOT COLUMN=’VALUE’;

1.1.2              Faster Inserts
1) To execute several inserts, deletes or updates you can disable the
logging, this means you will have to do a full back up after that, because
it eliminates redo generation, needed for backups in archivelog mode.

You can do directly in the table

ALTER TABLE ADM.ANF_RATIOS_ME NOLOGGING;



2) insert /*+ append */ into…, remember indexes will save log information.

, this bypasses undo generation, your table will have to be commit, before
issuing this command and after issuing  if you want to access it again.

This is completely safe.



3) Analyze the use of import or load utilities to load that table or data,
usually is the fastest.



4) When you have to insert (if not exists) and update if it exists you can
use MERGE command



5) If you are using loops, to insert data, use bulk collect.


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
My weekly tip: massive insert and update

Other related posts:
