Re: Oracle8i Partitioning

I get a little twitchy when someone bounces out the number 1,000 for no apparent reason. It makes me think they've seen a presentation where someone has done a couple of silly experiments and then declares:

    "just slice everything into 1,000 partitions it's     a great performance benefit"

I would rather see the number explained as (say) daily partitions for 3 years (1,100 partitions which can probably be made safe), or hourly partitions for 6 weeks (1,000 partitions, which could well be a disaster).

The basic answer to your question is - Yes, there are people using more than 1,000 partitions in a single segment, I have seen a few of them.

BUT -
Why do you want to use partitioning ? Administration or performance, and were you thinking range, hash or composite (or list in 9.0.1).

Which version of Oracle are you using ? partitions in 8.1.7 perform efficiently in far more circumstances than they do in versions 8.1.5 and 8.1.6.

Are your queries going to access small volumes of data, or large volumes of data ? This affects the number of partitions you should consider, and the precision with which your queries must do partition elimination.

How many different indexes are there going to be on each partitioned table - the number of partitions relevant to a query is not just the table partition count, don't forget that a table of 1,000 partitions with 3 local indexes actually involves 4,000 partitions in total.

How many partitioned tables will appear simultaneously in a single query - can you ensure that partition elimination is not disabled in some of them by the nature of the join. Can you ensure that you can take advantage of partition-wise joins.

Will you be using parallel query - there are times when a parallel query against partitioned tables will operate MUCH more slowly than a serial query because the parallel path removes the option for partition elimination.

In summary:

    You need the newest versions of Oracle for optimal performance

    It is not unreasonable to have tables with 1,000 to 2,000 partitions,     and I have seen systems survive on 10,000 partitions with minimal     indexing and simple queries.

    For numbers of partitions in this range, your partition elimination had     better be very good.

    If you exchange, drop, split, merge or add partitions too frequently     then there is a massive impact on parse times - make sure you check     this point when doing initial performance testing.

    Use local indexing - or you lose the administrative benefits of partitions.

    Keep index numbers to a minimum

    Avoid synthetic (meaningless sequence) keys

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


D Hoffman wrote in message
<8b548.64068$fg.4265853_at_bin7.nnrp.aus1.giganews.com>...


>

>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?

>

>Donna

>

>

>

>