Re: re-indexing per statement or per row?

Thomas

It is nice if you can do it in one sql statement. But you need a huge rollback segment.

I our case, ervery day, we need to insert 3m rows into one table, we have to use

declare

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

    for x in c1
    loop

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

otherwise, your rollback segment will grow so big that no other transaction can go. You can argue that you can use a seperate big rollback segment, but how can you prevent other people use this same rollback segment w/o cause error "snapshot too old"?

Just my little tip of the day.

"Thomas Kyte" <tkyte_at_oracle.com> wrote in message news:a8n7mr0d49_at_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 Sun Apr 07 2002 - 07:47:13 CDT