Re: Index strategy for huge partitioned table

"Dereck L. Dietz" <dietzdl_at_ameritech.net> wrote in message news:YGKGg.12233$kO3.8712_at_newssvr12.news.prodigy.com...
> 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.
>

Hi Dereck

Big topic this and I don't really have the time to do it justice but a couple of little points.

The advantage of local indexes is generally due to maintenance/administrative reasons. It means you can perform maintenance tasks such as dropping, splitting, exchanging, etc. table partitions and the such and only the local index is impacted, rather than making the whole index unusable (and requiring a full rebuild) or making such operations really slow by updating global indexes on the fly with the update global index clause. There are also availability advantages if there's a "problem" with a table partition. So I'm a little puzzled by the "maintenance nightmare" your DBA fears so much, it must be a "maintenance nightmare" to say drop/split/exchange/move/etc. a partition and have to rebuild 18 indexes ? Note also that global indexes can be also partitioned.

However, the advantages of local indexes from a *performance* perspective is often overstated. Yes a local index partition would likely be smaller than it's chubbier global cousin and yes it might even be small enough to reduce the height of the index by a level (or it might not). But what's an extra I/O or so among friends and if the query doesn't perform partitioning pruning, there'll be a performance hit as each local index would need to be accessed rather than the not so bad after all solitary global index.

So I would doubt that simply making all your indexes local will dramatically improve performance (although it might simplify some partition operations and may even reduce some DBA nightmares) ...

However, all this really just goes back to standard SQL tuning. Where is all the time being spent by these slow queries, are there indexes (global or otherwise) that it should be using that it's not because the suitable indexes don't exist or the CBO is not picking them, or table joins are the wrong type or the wrong order because the statistics are inaccurate, as this is a data warehouse bitmaps indexes may help as perhaps star transformations might improve plans, perhaps the table is not partitioned appropriately to take advantage of partition pruning or partition likewise joins, etc, etc, etc ...

If you post an example query that's performing poorly with it's execution plan and other relevant details, I'm sure some clever bugger will be able to explain (no pun intended) how to potentially improve its performance.

Cheers

Richard Received on Wed Aug 23 2006 - 07:59:46 CDT