Re: re-indexing per statement or per row?

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 6 Apr 2002 08:28:11 -0800
Message-ID: <a8n7mr0d49@drn.newsguy.com>

In article <uase5nsnt4u5bd_at_corp.supernews.com>, "Kenny says...
>
>If a table has indexes, are the indexes updated per statement or per row for
>insert/update operations?
>Practically, this affects the choice between bulk sql and putting sql
>statements in a loop.
>
>Kenny
>
>

In addition to Jonathan's "thought for the day", I'll add my "mantra of the day"

If you can do it in a single sql statement do it (never code procedurally what you can do in a single statement)

if you cannot do it in a single sql statment, consider plsql as the next best choice

if you cannot do it in plsql, perhaps a java stored procedure can help you out.

C external routines are the last choice.

Never do procedurally what you can do in a single statement. I cannot tell you how many times I see code like this:

declare

   cursor c1 is select .......
begin

   for x in c1
   loop

        insert into t values ( c1.a, .... );    end loop;
end;

that should just be:

insert into t select ....;

period. Or the old:

   for x in ( select * from t )
   loop

        x.a := f(x.b);
        update t set a = x.a where pk = x.pk;

   end loop

that should just be

 update t set a = f(b);

period. (i know, someone will say "but we throw commits i there to save resources". Problems with that argument are typically:

o it takes more resources to commit periodically. it generates more redo, it makes you wait for log file syncs, it moves the data out of the database and back in.

o the cost of an 18gig disk drive that you just buy -- hook up to the system and use for that one update and then throw away (yes, just throw it away) is typically less then what you have to pay someone to develop and test the procedural code......

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Sat Apr 06 2002 - 10:28:11 CST