Re: Append hint

On Sat, 26 May 2001 07:06:55 +0100, "Alan" <alan_at_NOSPAM.opcomp.demon.co.uk> wrote:

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

AFAIK, it does a full re-build. Ie, it doesn't sort of start where the load stopped. It's either the lot or nothing. Except of course if you are using partitioning.

>
>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 think if you want to re-build at max speed, there is a case for doing it manually. At least then you can control the parallelism, temp tablespace, allocation, etc.

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

Hmmmmm, I see what you mean. Actually, given the increase in speed of bulk loads, IME it's faster to drop the indexes and re-build them at the end. With a SMP system, the re-build doesn't really take too long and the advantages are many.

It's not a good idea to add a lot of new entries to an existing index, not at least as a bulk operation. They get messed up easy. Reverse indexes can help but you gotta be carefull with those, they are not good for range scans.

Another thing that helps a lot in datawarehouse is to use partitioning. If you do one of those for each of your bulk loads, then all you gotta do is build the index for that partition.

Wroks really well and if you look at the way those are handled by other databases (DB2 namely), it's exactly what they do: split and spread the load across partitions. Then just re-build the one that is affected. You can do the same in ORACLE.

Cheers
Nuno Souto
nsouto_at_bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Sat May 26 2001 - 05:51:40 CDT