Index Maintenance for DW environment
- From: <thomasjd@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 11 Aug 2006 10:17:34 -0400
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
Other related posts:
