RE: Index Maintenance for DW environment

Jeff

> Not only are these large scripts cumbersome to maintain

The simples way, which is used in some DWHs I know, is to generate/extract the DDL for the CREATE INDEX from the DD before dropping them.

> but we are experiencing performance issues in not having
> these indexes tactically available during the nightly run.

For that problem, as you wrote, only a granular control of the indexes that should be dropped may help.

Since you are using Power Center if the indexes are in place you wont be able to use direct inserts (which are enabled when in Power Center you specify "bulk inserts", or something like that...).

To speed-up the index creation itself you can use nologging and parallelization (which in turn activates direct reads).

HTH
Chris

--
http://www.freelists.org/webpage/oracle-l