Re: Index Maintenance for DW environment

It might be time for you to completely re-think your data load strategy.

I've got a DW with very "wide" tables (150+ columns, most of them indexed). Individual inserts to the tables take 30+ seconds and I've got to load 2M rows at a time. Dropping and creating 150 indexes on a 250M row table takes weeks, not hours. So we've got the same conundrum you do. (In fact, you've probably got a second issue related to gathering statistics about the same table; as it grows your stats jobs grow in length too, unless you're jamming in fake stats with dbms_stats jobs).

Solution: partition your fact table and turn all your indexes into local indexes instead of global (bitmap preferable for doing star transformations and bitmap merge joins) Then do your data loads into a table off to the side of the exact same structure (minus the partitioning clause) as your main fact table. Create indexes, gather stats on the side table (with a smaller data set, even a 2M row table should be able to finish indexes and stats within minutes), then add a new empty partition to the fact table and alter table fact_table exchange partition new_partition with side_table.

The actual alter table command takes only a few seconds, and immediately all your indexes are updated and active, your partition stats are collected and you've done it all with little to no downtime. If you're in 9i, we ran into a bug that required a hidden parameter to be set (_minimal_stats_aggregation=FALSE) in order for the stats to exchange properly. Otherwise, as long as the tables are exactly the same structure, have exactly the same indexes created, same constraints, same null/non-null definition, the alter table exchange partition process works great.

hope this helps, todd

>
> Our shop is new to large-scale datawarehouse projects=2E=A0 We are findi=
> ng our current index maintenance strategy for nightly jobs to be unworka=
> ble=2C as it consists of dropping all indexes before the nightly run=2C =
> and building them anew after the run is completed=2C via large SQL scri=
> pts=2E
>
> Not only are these large scripts cumbersome to maintain=2C but we are ex=
> periencing performance issues in not having these indexes tactically ava=
> ilable during the nightly run=2E=A0 What we need is a strategy that allo=
> ws us to drop/build indexes in a more granular=2C controlled fashion=2E =
> I had thought of storing all index DDL in a table and using generic stor=
> ed procedures that can be called to drop / build the indexes by table=5F=
> name=2E
>
> I also thought=A0 of using stored procedures to make indexes unusable an=
> d to rebuild them rather than drop/build=2C but as we are using Informat=
> ica=2C=A0 I=27m not sure as to the feasbility of getting Informatica to =
> perform alter=A0 sessions to skip unusable=2C and so forth=2E=A0 =
>
> Any ideas or advice would be appreciated!
>
> Thanks=2C
> Jeff

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