RE: sane number of the table partitions in DWH
Date: Mon, 30 Mar 2009 13:02:12 -0400
Message-ID: <812CA69082B246E98294779D8F9A66BD_at_KenPC>
How many rows would each partition have with 200k partitions? I have a 1tb table with 4-5 billion rows so if your table was similar to mine (5 billion rows/200k partitions) you'd have 25k rows per partition which seems pretty small to me unless the partition key was in every query.
Unfortunately, our partitioning scheme doesn't have enough partitions as we partition by quarter and have up to 1 billions rows per partition, but the way the third party app is (poorly) designed we don't have much choice, so I can't give you advice on that number other than it seems remarkably high. I'd like to hear what you end up doing and how it works out.
Thanks,
Ken
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Milen Kulev
Sent: Monday, March 30, 2009 12:48 PM
To: oracle-l_at_freelists.org
Subject: sane number of the table partitions in DWH
Hallo Listers,
I am on site by a pretty creative customer.
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 !).
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 ?
Fact tables have sizes ~ 700 - 1000 GB.
At the moment partitioning schmeme is range(date)/hash(location_id)
NO partition exchange is used/and will not be used.
Data is relatively volatile (UPDATES, DELETES) + usual INSERTS (some kind
of operational DWH ).
Application type = DWH ( ~ 4.5 TB)
Application sends SQLs with literals (static partition pruning)
Oracle Version = 10.2.0.4
OS = Suse 9 SP4
I will be grateful for any links/ presentations , experiences, relevant ML notes etc ...
Best Regards
Milen
--
Neu: GMX FreeDSL Komplettanschluss mit DSL 6.000 Flatrate + Telefonanschluss
für nur 17,95 Euro/mtl.!* http://dsl.gmx.de/?ac=OM.AD.PD003K11308T4569a
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Mar 30 2009 - 12:02:12 CDT