Re: Append hint

"Nuno Souto" <nsouto_at_nsw.bigpond.net.au.nospam> wrote in message news:3b0f1579.4539334_at_news-server...
> On Fri, 25 May 2001 19:41:34 +0100, "Alan"
> <alan_at_NOSPAM.opcomp.demon.co.uk> wrote:
>
> >
> To do this, the RDBMS has to disable indexes because there is no way
> the bulk load will work on indexes. And if indexes are enabled, every
> row loaded will cause an index insertion, defeating the purpose of the
> bulk load.
>
> AFAIK, there is no way of using APPEND and leave indexes enabled. The
> APPEND will only work if they are disabled, although you may have
> added it to the statement.
>
> Anybody else has found a way?
>
> Cheers
> Nuno Souto

Yes, I agree, although you do not have to disable the indexes yourself, Oracle does it for you, and if you are using Sql*Loader, you can see a log of the fact that Oracle had to populate the index after the load.

What I'm trying to understand is:- when Oracle updates the index, does it do a full re-build (I think not) or does it have a fast bulk method of updating (in other words, does it 'keep' details of the index keys as it is loading).

Also, is there any circumstance where you would want to manually disable/rebuild your indexes when using the APPEND hint or should you always just allow Oracle to manage this.

I often see in Datawarehouse applications, indexes being disabled before a load and fully rebuilt afterwards. This strikes me as a costly excercise rather than a fast load method. Data is not usually 'deleted' from this type of table, so it would seem sensible to always use the APPEND hint (PARALLEL is often used which forces an APPEND) and, therefore, allow Oracle to manage your indexing. Received on Sat May 26 2001 - 01:06:55 CDT