Re: sane number of the table partitions in DWH
- From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
- To: Kurt-Franke@xxxxxx
- Date: Mon, 30 Mar 2009 22:31:11 -0500
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
>
>
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
