Hi, The indexes in the partiitoned tables should be based upon the
facts 1. Usability 2. maintenance. It all depends upon the column which
is part of the index. For any column which is being indexed and is also
the partitioning key, should be indexed as local for better perforance.
If that column is not partitioned column better to make index global in
that case.
Dereck L. Dietz wrote:
> 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 - 17:01:47 CDT
