The only rule I know of is that the number you choose should be a factor
of 2 (2, 4, 8, 16, 32, etc.). As for the optimal number, for us it was
a trial and error sort of thing.

With a table of that size you may wish to use list/hash or range/hash
partitioning; we have a table of about the same size in which we use a
list/hash partition - the primary partition is by date (quarters), then
we hash partition into 32 subparts. So each quarter's worth of data
contains 32 partitions. This works very well for us.

-Paul

-----Original Message-----
From: Jankiewicz Bill - wjanki
[mailto:oracledba-ezmlmshield-x79738816.[Email address protected]
Sent: Friday, October 05, 2007 3:21 PM
To: LazyDBA Discussion
Subject: RE: hash partitioning optimal number of partitions

Which one 2,4,8,16,32,64? Do you pick one, monitor performance and then
make adjustments? Or is a million rows per partition your formula?

This table has 320,000,000 rows.

-----Original Message-----
From: ann [mailto:ann.[Email address protected]
Sent: Friday, October 05, 2007 2:18 PM
To: Jankiewicz Bill - wjanki
Subject: Re: hash partitioning optimal number of partitions

**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text


2,4,8,16,32,64.......... Seems like about a million rows per partition

work for me. I look at the number of unique keys also.

Ann Habel
816-823-4040
816-729-2631 (cell)
DBA
Architecture and Infrastructure Management Team
Heartland Financial Systems Division - 6bd




"Jankiewicz Bill - wjanki "
[Email address protected]
10/05/2007 02:11 PM

To
"LazyDBA Discussion" [Email address protected]
cc

Subject
hash partitioning optimal number of partitions






Anyone have a formula or methodology for determining the optimal number
of partitions in a hash partitioned table?

************************************************************************
*
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be
legally privileged.

If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.

If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.

Thank you.
************************************************************************
*


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html






---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information or otherwise protected by law. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

Oracle LazyDBA home page