Re: Creating indexes takes too long

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<pan.2003.03.13.11.37.51.147380_at_yahoo.com.au>...

> Oh dear. Look: Oracle isn't SQl Server, and attempts to make them do the
> same thing are just doomed to disaster. Never mind that a 10-column index,
> representing about 20% of the largest tables you have, sounds like
> distinctly dodgy design in the first place.
>
> But if you must go down this appalling road, then ask yourself what Oracle
> is doing that SQL Server isn't: generating redo, perhaps? Generating undo?
> Sorting in memory? Or sorting on disk?
>
> Try using the 'create index NOLOGGING' syntax to keep the redo to a
> minimum. Look at your sort_area_size to make sure the sort needed to
> populate the index isn't hitting disk.
>
> Start analyzing what waits are happening on the database during the index
> builds (welcome to Statspack).
>
> If you don't know how to run statspack, find out (tahiti.oracle.com). But
> if you don't know how to interpret statspack, then go to www.oraperf.com
> and get it analyzed for free there.
>
> Regards
> HJR
Hello Howard,

We are already using NOLOGGING syntax, but we haven't tuned the db parameters, so my colleague tried increasing the size of sort_area_size to 4mb, and it reduced the time by 1/3rd. I think he is going to find out what is the reasonable size for this parameter and also what other parameters are going to have an impact. Since none of us have done this earlier, we are going one step at a time. Thanks for your input, I appreciate it.

Hari Received on Mon Mar 17 2003 - 14:55:28 CST