Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> 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-lReceived on Fri Aug 11 2006 - 09:17:34 CDT
![]() |
![]() |