Re: Index Maintenance for DW environment

Jeff,

To put it concisely, partitioning your tables, making most (if not all) indexes local (most especially bitmap indexes), and using EXCHANGE PARTITION load-strategy is your *only* hope for a real lasting solution.   Everything else (i.e. PARALLEL, NOLOGGING) is useful, but only a temporary stop-gap, without partitioning.

Hope this helps...

-Tim

thomasjd_at_insightbb.com wrote:
> 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