Re: Oracle8i Partitioning

Nuno Souto wrote:
>
> D Hoffman doodled thusly:
>
> >
> >Is there anyone using partitioning on a segment with more
> >than 1000 partitions? If so, are there any risks or noticeable impacts to
> >the database?
> >
>
> Jonathan for sure must have done this before. He's usually the one
> that stress tests Oracle in extreme situations. If he doesn't drop in
> on this, go to his www site:
>
> http://www.jlcomp.demon.co.uk/
>
> and ask the man himself.
>
> Cheers
> Nuno Souto
> nsouto_at_optushome.com.au.nospam

Not that I'mn trying to stand on the shoulders of giants, I've done a similar exercise with approx 2000 partitions (5 years of 1 day partitions) - after having a discussion with Johnathan of course :-)

All the standard partition stuff - add/drop/blah/blah works as per normal. The main things that caused us grief were:

1. parse times could be enormous for relatively simple queries unless partition elimination was relatively obviously to the optimizer. Our best was 8 minutes determining a plan (which then ran in 5 seconds)
2. I would imagine that lots of partitions means lots of data (as it did in our case), which means local indexes. All well and good, but "poorly" written queries that end up probing each index partition take longer then expected.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."