Re: re-indexing per statement or per row?

Thomas Kyte wrote:
>
> 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

And just to reinforce Tom's point - from a shopping web site

(IDE) disk 160G £205
(SCSI) disk 36.9GB £258.50

You don't get a lot of developer time for £250

:-)

Cheers
Connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."