| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> My weekly tip: massive insert and update
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 becreate table2 as select … from table1;
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.
 |
 |