Re: sane number of the table partitions in DWH

  Actually, partition count limit in 10gR1 is 64K-1, but 10gR2 is 1024K-1. So, 10.2.0.4 should support 1million+ partitions.

http://cs.felk.cvut.cz/10gr2/server.102/b14237/limits003.htm#sthref4186

 I don't know, much about complexity of your application SQL or table design as to whether it is composite partitioned, range or hash partitioned, partition key properties , partition pruning etc, but you might want to take couple of heavily executed SQL and test it. Theoritically speaking, partition pruning is not a costly operation.

  I will share my experience though. I had a client with 50K+ partitions in a table (simple range partitioning on a number column) and uses literals heavily. We didn't see much parsing issues when we added (actually, plsql code to split last partition in a binary tree fashion to be exact) 30,000 partitions over a weekend. As long as, partition and global statistics are accurate, you shouldn't see much increase in SQL execution time.

  YMMV :-) Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com Specialists in Performance, Recovery and EBS11i Blog: http://orainternals.wordpress.com

Forwarded Message:

On Mon, Mar 30, 2009 at 2:30 PM, Kurt Franke <Kurt-Franke_at_web.de> wrote: Milen,
Developers want to partition a (fact) table(s) to the smallest slice possible.

The number of calculated/estimated partitions is 200 000 - 300 000 (Yep, this is NOT a typing error !).the oracle limit for the number of partitions per table is 64k - 1 in database version 10 My questions:

1. What is the "manageable" number of partitions (from performance point of view == parse times) that I you have seen by clients.
2. Any parameters (also underscore ...) to reduce the hard-parse times (because of the enormous amount of partitions) ?

> > 3) If I have so many partitions , what is the influence on the
> fragmentation of the shared pool? Any experiences ?
> > 4) Is multi-column partitioning a save bet (bugs) ?
> > Is is running stable ?
> > Are any special one-off patches on top of 10.2.0.4 needed ?
>
> the patch is named version 11g which then will allow 1024k - 1 partitions
> per table
>
>
> regards
>
>
> kf
>
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l