create indexes

[xyz2000]

I need to create 12 indexes on a large table (100 GB). indexes size from 1GB to 20 GB.

So far, it takes 1-3 hours per indexes. Any way to tuning init. parameters to get better performance?
Database is 9i.
So far I used
pga_aggregate_target 1200M
workarea_size_policy Auto

what do you think if I used sort_area_size (sort_area_retained_size)? What number will be proper?

Thanks in advance

xyz2000

[slimdave]

Partitioned or not partioned table, and indexes local or global?

[xyz2000]

not partioned table

[Horace]

With workarea_size_policy set to auto, your sort_area_size parameter will be ignored. If this in Win2K system, then I advise setting pga_aggregate_target slightly higher, maybe 1800 M, then monitor it with the PGA advisory.

I assume you are creating the indexes in parallel mode, and in NOLOGGING mode ?

[Horace]

You could also kick off 12 parallel sessions, each building one index. You'd need to monitor CPU and memory (temp segment) usage.

[xyz2000]

index created in parallel 8 and nologging. I am thinking about set work_area_policy to manual and set sort_area_size and.. to see if that will speed up a little bit. Indexes are created in sequence.

[Horace]

I'd leave work_area_policy in auto if you're running in parallel, it's (probably) smarter than you at allocating space for parallel slaves, and will use up to the maximum available pga space.

I've just done a test on a 100G table building a b-tree index using parallel 8 nologging on a 150 million row table with varchar2 column cardinality at 13M. Time to create was 15 mins. This is on a Win2K server with 8 zeon 2G CPUs 4G ram.

Are you limited by your hardware?