Re: Update a table with no logging?

"Alan" <a_at_a> wrote in message
news:989872743.27642.0.nnrp-01.9e985e86_at_news.demon.co.uk...
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> > As said by Howard Rogers
> today in this group: nologging applies only to
> > *very specific* types of DML.
> > Your suggestion is not going to work.
> > If he is on 8i, he should be able to use the /*+APPEND*/ hint, which
 will
> > add the data in *direct* mode (so indexes need to be rebuild).
> >
> > Hth,
> >
> > Sybrand Bakker, Oracle DBA
> >
>
> Can you clarify what you mean by "so indexes need to be rebuild". Doesn't
> Oracle update the indexs automatically, in the same way that it does for
 an
> sql*loader direct path load.
>
>

Sybrand has told him to use the APPEND hint. That means that Oracle constructs entire blocks of the new data in memory, and slams them down onto disk, *above* the high water mark. When the insert is finished (ie, a commit is issued) the high water mark is adjusted so that the new blocks are beneath it.

The process makes the insert really fast -because we know we are going to be writing into blocks above the high watermark, we can be fairly cavalier in the way we do it... we're guaranteed there's no existing data there that has to be slotted in (by the very definition of what a HWM represents).

That makes the load extremely fast.

But you never get nothing for free with Oracle. The price to pay for bluntly slamming complete blocks down onto disk is that there is NO clever, subtle update of the relevant indexes, as there would be with a more subtle, clever normal insert. So, yes... your indexes are toast, and you have to rebuild them. Allegedly, you still win in the end: the load goes so much faster that the extra time spent rebuilding indexes is usually not enough to counteract the gain (that's the theory, anyway).

The exact same thing happens to SQL Loader loads *if* they are done in direct path (direct=Y). If you do a conventional SQL Load, then all you are doing is a highly automated set of normal inserts, so of course the indexes are updated (as they would be if *you* were personally typing in the inserts).

Regards
HJR

--
=============================!!=============================
The views expressed are my own only, and definitely NOT those of Oracle
Corporation
=============================!!=============================