RE: Index Maintenance for DW environment

  • From: "Guang Mei" <GMei@xxxxxx>
  • To: <thomasjd@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 11 Aug 2006 10:25:51 -0400

Do you use nologging when rebuilding you rindexes? That could speed things up 
quite a bit.
 
Guang

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On 
Behalf Of thomasjd@xxxxxxxxxxxxx
Sent: Friday, August 11, 2006 10:18 AM
To: oracle-l@xxxxxxxxxxxxx
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




Other related posts: