Re: Performance problem with partitioned indexes & tables

Hi All,

And just for the record, I certainly never suggested I was against local indexes. I clearly mentioned that there are pros and cons to local/global indexes and that it's a case of horses for courses (as it usually is).

Local Indexes are extremely useful when used appropriately and have clear maintenance advantages over global indexes. However, when all local index partitions need to accessed, then issues such as this can arise. The performance implications in this example, suggest that the local horse may not be the right one for this course (not while it's a wet track anyway :)

Cheers

Richard

Jonathan Luis wrote:
>
> Comments inline.
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Next Seminars
> UK Sept, Nov
> USA x 2 November
>
> http://www.jlcomp.demon.co.uk/seminar.html
>
> Michael Burden wrote in message
> <3bbc0756.0208080127.61a916cb_at_posting.google.com>...
> >
> >Everyone seems against local indexes but why.
> >
>
> This seems to be a rather extreme comment, given
> that only two other people have participated in this
> thread so far.
>
> Just for the record - local indexes are a 'good thing'
> and can be used very effectively. The arithmetic in
> the example you give is, however, well-known as
> defining a potential overhead.
>
> >
> >Now as the data is striped (or perhaps even better each partition is
> >on a different disk) each of the 10 partitions can be run in parallel
> >and so the actual response time could be quicker.
> >
>
> Your arithmetic is perfectly sound, but the response
> time you are seeing is not as expected. Perhaps the
> paragraph above is a hint at the real problem - are
> you making this query run in parallel - if so, how many
> ways, and is it possible that most of the excess 3-4
> seconds is in the PX setup ?