Re: Index Maintenance for DW environment

Thanks for all the feedback.

1. Yes we are using nologging and parallelism
2. No to partitioning -- but I'm pushing for it. I might add this is a Siebel OLAP/Analytics database that I inherited after the fact. I've perused OTN/Metalink/Siebel and found nearly zilch on best practices/tuning for Siebel on Oracle.
3. The big issue is the bitmap indexes in reality -- we have over 750 of them being dropped and rebuilt each night.
4. As for stats, I got a baseline saved to a stats table, and I only allow them to restore stats via a stored procedure I wrote. I don't gather stats until they are stale to the point of degrading performance.

Thanks,
Jeff

Niall Litchfield wrote:

> On 8/11/06, *thomasjd_at_insightbb.com <mailto:thomasjd_at_insightbb.com>*
> <thomasjd_at_insightbb.com <mailto: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.
>
>
> I am not a DW DBA. However it sounds at least possible to me that
> appropriate use of partitioning might be an option for you.
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>

--
http://www.freelists.org/webpage/oracle-l