Index strategy for huge partitioned table

Datawarehouse environment. Oracle 10g version 1 (not sure of exact release number).

Table partitioned into 10 partitions with total row count of approximately 70 million rows. Currently the response time when querying this table isn't what could be called the best.

Currently 18 indexes - all B-Tree incidentally - are on this table and all are global.

Our "DBA" stated that partitioning the indexes into local indexes would be a "maintenance nightmare". With a partitioned table of this size, is there any reason why the indexes themselves should not also be partitioned (I'm trying to come up with a reason why the DBA seems so against it)? Should ALL be partitioned or just a few be partitioned?

I'm asking because my director asked me to look at the indexes for this table and make suggestions on how to make things better. I'd like to make as many of the low cardinality indexes bitmaps as I can as all but three of the indexes have cardinalities of less than 5%; partition as many indexes as would be practical to help performance and any other suggestions that may be forthcoming.

Thanks in advance. Received on Tue Aug 22 2006 - 16:29:28 CDT