RE: Index Maintenance for DW environment

Thomas,
In the data warehouse I recently built I wrote a stored procedure that takes an owner and table name as a parameter and then disables or enables all the bitmap indexes on table. I use a similar process to reload staging tables from the source system but for b tree indexes. The disable process included a dynamic sql call to alter the session to skip the unusable indexes. I call these procedures at the beginning and end of a load. Although I haven't used informatica in a while I remember it can call pl/sql quite easily. If can help, feel free to contact me privately.  

Ken Naim  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of thomasjd_at_insightbb.com
Sent: Friday, August 11, 2006 9:18 AM
To: oracle-l_at_freelists.org
Subject: Index Maintenance for DW environment  

Our shop is new to large-scale datawarehouse projects. We are finding our current index maintenance strategy for nightly jobs to be unworkable, as it consists of dropping all indexes before the nightly run, and building them anew after the run is completed, via large SQL scripts.

Not only are these large scripts cumbersome to maintain, but we are experiencing performance issues in not having these indexes tactically available during the nightly run. What we need is a strategy that allows us to drop/build indexes in a more granular, controlled fashion. I had thought of storing all index DDL in a table and using generic stored procedures that can be called to drop / build the indexes by table_name.

I also thought of using stored procedures to make indexes unusable and to rebuild them rather than drop/build, but as we are using Informatica, I'm not sure as to the feasbility of getting Informatica to perform alter sessions to skip unusable, and so forth.

Any ideas or advice would be appreciated!

Thanks,
Jeff

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