Re: re-indexing per statement or per row?

In article <ldXr8.6008$F13.2239_at_nwrddc03.gnilink.net>, "joe says...
>
>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"?
>

you will INDUCE the snapshot too old by committing periodically. That is one way to CAUSE the 1555. frequent commits.

>Just my little tip of the day.
>

that tip will slow you way down.

why not

insert /*+ append */ into t select......

I'll just reiterate my tip of the day:

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_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
>>
>
>

--
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