RE: Index Maintenance for DW environment
- From: "Ken Naim" <kennaim@xxxxxxxxx>
- To: <thomasjd@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 11 Aug 2006 09:38:45 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of thomasjd@xxxxxxxxxxxxx
Sent: Friday, August 11, 2006 9: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:
