Re: sane number of the table partitions in DWH

Milen

  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@xxxxxx> 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
>
>
>

Other related posts: