Re: re-indexing per statement or per row?

Hi,

if recoverability is not an issue (please don't flame me for that...), you might consider

INSERT /*+ APPEND */
INTO t_destination
SELECT ...
FROM t_source

which only generates sooo little redo and undo.

Otherwise you HAVE to configure your RBS to appropriate sizes!

On Sun, 07 Apr 2002 12:47:13 GMT, "joe sath" <dbadba62_at_hotmail.com> wrote:

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

regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Sun Apr 07 2002 - 08:08:06 CDT