Re: Alternate Indexes

On 08-Jan-98 17:41:28 Syed Ali wrote:
>I have an alternate index defined on a large table (3.6 million rows). I
>load data in this table using SQL*LOADER utility in DIRECT Load mode.
>The index is automatically loaded after the data has been loaded. Which
>is a better approach ? Create index after the data load or create index
>before data load in DIRECT load mode (as I am doing) ? Does it even
>matter when you load the data using DIRECT load mode ?
 

>I'll appreciate any response.
>S A

I have observed that loading the index (i.e. doing the load while the index exists) needs more temporary tablespace than creating it afterwards. We were doing some loads into tables with multiple indexes. One load failed because the temporary tablespace was full. But I was able to create the index afterwards.
I did not do any benchmarks concerning the performance of either method.

Of course it matters, if you are usin direct path or not. When using conventional path, the data is put through SQL and every index slows down the inserts. Additionally, since there are many inserts, the indexes may become quite unbalanced degrading further performance. So for conventional loads I would prefer creating the indexes afterwards. When using direct path, first the indexes are put into direct load state, second the data is load and third the indexes are rebuild. This should be as performant as manually creating the indexes afterwards except for that I wrote above.
Another thing to cancider is if anything goes wrong while the load, all indexes are left in direct load state. They are useless and have to be rebiuld.

Hope that helps,
Lothar

--
Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Schulstr. 12            | lothar.armbruester_at_t-online.de
D-65375 Oestrich-Winkel |