Re: SQLLDR Performance & Indexes

"Rajan" <vahee_san_at_hotmail.com> wrote in message news:<3c4841d5_at_MAIL.mhogaming.com>...
> Hi,
> I have a empty table with 3 indexes set on it. I used SQLLDR to upload 4
> million records to it (Direct Load). It took about 2 hrs to do this.
>
> But when I put SKIP_INDEX_MAINTENANCE=true, it just took 8 minutes to upload
> the data. Then I issued ALTER INDEX indexname REBUILD; for each indexes and
> it took about 45 minutes for each indexes. So, if we consider the total
> time, it all adds up to the same figure of 2 Hrs.
>
> Is there a better way of doing this to improve the performance ? I read in
> many articles that when we do a Direct Load we should drop the indexes. But,
> if index rebuilding is going to take this much of time, then whats the use ?
>
> Thanks,
> Rajan

You did use the NOLOGGING clause on the REBUILD and the PARALLEL <n> clause when using Oracle EE on a SMP machine. You do have an appropiately configured sort_area_size and/or temporary tablespace? The REBUILD alone definitely shouldn't take that long.

Hth

Sybrand Bakker
Senior Oracle DBA Received on Sat Jan 19 2002 - 13:43:40 CET