As long as you are on 9ir2, you might want to create/rebuild the index
using the parallel nologging command. To give you and example of
timings, I rebuild on a an 8 processor sun box, 240 indexes on a 350Gb
database in under two hours. The key is nologging and parallel, set the
parallel equal to your cpu's..An example would be...

This is for a rebuild...

Alter index INDEX_NAME rebuild nologging online force parallel
storage(initial XXSIZE next XXSIZE maxextents unlimited pctincrease 0)
compute statistics tablespace TABLESPACE_NAME;

Previous to this I set parallelism on all the tables with the
following....

Alter table TABLE_NAME parallel;

The script to generate these are....

(parallel)

Select 'ALTER TABLE '|| table_name || ' PARALLEL;'
From user_tables
/

(indexes)

Select 'ALTER INDEX '|| index_name ||' rebuild nologging online force
parallel storage(initial 10M next 10M maxextents unlimited pctincrease
0) compute statistics tablespace TABLESPACE_NAME;'
From user_indexes
/

Hope that helps....


Edward J. Grimm


-----Original Message-----
From: (Frank Dodgers)
[mailto:oracledba-ezmlmshield-x34179173.[Email address protected]
Sent: Thursday, February 24, 2005 9:27 AM
To: LazyDBA Discussion
Subject: Create Index Taking Too Long

I am able to move the 220,000,000 row table using a create table as
select in 3 minutes but it takes 13 minutes to create an index on the
table. I appreciate any advice you can provide.

Thanks, Frank


--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com To unsubscribe:
see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com By using this list you agree
to these terms:http://www.lazydba.com/legal.html


Oracle LazyDBA home page