This discussion is archived
7 Replies Latest reply: Feb 18, 2013 10:47 AM by rp0428 RSS

Hash - partitioning -  recommended size of each hash-partition

user5716448 Explorer
Currently Being Moderated
Hi,

Using Oracle 11.2.0.3 and evaluating various partitioning strategies

1) Either we will use no partitioning

2) raneg partitioning

3) Hash partitioning.

In process of building same table according to each of these approaches in order to make final decision on partitioning of this table.

With reagrds hash partitions, what is the recommended size of each hash partition.

Have a 116gb table.


Thanks
  • 1. Re: Hash - partitioning -  recommended size of each hash-partition
    John Stegeman Oracle ACE
    Currently Being Moderated
    Each partition should be somewhere between 0 bytes and 5,000 petabytes.

    </sarcasm>

    If you could perhaps tell us more about what you hope to accomplish with partitioning, you just might get a more intelligent answer :)
  • 2. Re: Hash - partitioning -  recommended size of each hash-partition
    user5716448 Explorer
    Currently Being Moderated
    Hi,

    We estimate a large number of queries won't use partition key in query so we unfortunately won't get massive beefit from partition pruning.


    We would get quikcer housekeeping of data with range partititioning.

    With hash partitioning we may get quicker join between the facts tbale and the largest associated dimension if we hash partition on the product id and similarly hash-partition the large dimension (partition-wise joins).

    Above all we need to run some tests to check if the queries may perform better on unpartitioned table.

    Our overriding concern is report performance arther than admin/housekeeping benefits.

    Thanks
  • 3. Re: Hash - partitioning -  recommended size of each hash-partition
    Dom Brooks Guru
    Currently Being Moderated
    I like Tim Gorman's analogy about partitioning.

    From http://www.freelists.org/post/oracle-l/DW-partitioning-and-archiving,1:
    "
    I like to think about partitioning as a "gun with two bullets", which is one way of looking how one chooses the
    partition key column and the sub-partition key column. If you think about facilitating data loading,
    archival/purge or tiered storage, and partition-pruning during query as three targets, you have two bullets
    to use in nailing those three targets. Sometimes one bullet can hit all three targets, sometimes two of them,
    sometimes only one."

    It sounds like a combination of range partition & hash subpartitioning might give you both quicker housekeeping and better query performance

    Also see:
    http://www.evdbt.com/OOW09%20DWScaling%20TGorman%2020091013.ppt


    Regarding the number of hash partitions, depends on how big (or small rather) you want them to be, but the number of partitions should be a [url http://jonathanlewis.wordpress.com/2009/09/21/hash-partitions-2/power of 2[/url].
  • 4. Re: Hash - partitioning -  recommended size of each hash-partition
    user5716448 Explorer
    Currently Being Moderated
    Thanks for advice.

    Will make power of 2 but is their a recommended size e.g on size 116 gb table would 64 be too many 2gb a partition or would 8 be better (20gb per partition apporx).

    Any guidance on that would be useful.

    Thanks
  • 5. Re: Hash - partitioning -  recommended size of each hash-partition
    rahulras Explorer
    Currently Being Moderated
    If you will land up in a situation where you will have to crearte non-partitioned index(es) on partitioned table, whichever type of partitioning you use, think 100 times if you really that partitioning (unless you are not going to drop any partitions ever)
  • 6. Re: Hash - partitioning -  recommended size of each hash-partition
    user5716448 Explorer
    Currently Being Moderated
    Thanks for advice.

    Anybody know optimum size for a hash partition in terms of GB or is it less significant from report query performance angle.

    Edited by: user5716448 on 18-Feb-2013 10:19
  • 7. Re: Hash - partitioning -  recommended size of each hash-partition
    rp0428 Guru
    Currently Being Moderated
    >
    Using Oracle 11.2.0.3 and evaluating various partitioning strategies
    >
    Ok - but why?

    It if ain't broke, don't fix it. The recommended series of steps is:

    1. Identify the problem that you are trying to solve.
    2. Identify the cause(s) of the problem.
    3. Determine possible solutions to eliminate/mitigate the problem
    4. Select one or two solutions for further evaluation and testing
    5. Implement your 'best' solution based on performance, cost, maintainability, etc

    Based only on what you posted you are at step 4 - having chosen partitioning and no partitioning as your 'solutions'

    Where is the information about steps one, two and three? Oh - here is some of it posted later
    >
    We estimate a large number of queries won't use partition key in query so we unfortunately won't get massive beefit from partition pruning.

    We would get quikcer housekeeping of data with range partititioning.

    With hash partitioning we may get quicker join between the facts tbale and the largest associated dimension if we hash partition on the product id and similarly hash-partition the large dimension (partition-wise joins).

    Above all we need to run some tests to check if the queries may perform better on unpartitioned table.
    >
    That is a start - that post includes PART of what you need for step one but it isn't nearly specific enough and it isn't complete.

    Don't start a project like this without a complete set of requirements: that is more important than anything else you do.

    The big MISSING piece is that you haven't stated that you even HAVE a problem that you are trying to solve. The statements you made would apply to many, or most, installations. You might get quicker housekeeping with range partitioning? Yep and you might make your 'large number of queries' that can't use a partition key worse.

    With hash partitioning you 'may get quicker join'? Never heard of that 'quicker join' term. Where did you read that? Partitioning is associated to the question: where IS my data. Whereeve the data is it can be joined in several different ways to other data. So I don't understand your statement about hash partitioning.

    1. What problem are you trying to solve?
    a. Performance? Of what? Your 'large number of queries won't use partition key'? Or other queries that could use a partition key? Be specific: identify ALL of your key existing queries that have a performance issue that you want to address.

    b. data maintenance? Of what? data loading? data off loading? Inserting? Deletes? Updates? Identify the specific data maintenance operations that you are doing and any issues or problems that you have with them. BE SPECIFIC!
    What kind of maintenance do you do? Is it based on a date? a date range? Will all of the data be on-line? Do you need to move the data from the main table to a history table in the same database? to a different database? Does the older data need to be read-only?
    Your requirements for maintenance will impact how you partition as well as whether to use separate tablespaces for the partitions or subpartitions.

    2. Do you license the PARALLEL option? What resources do you have to support that? If you partition a table but don't use PARALLEL then operations on multiple partitions will be serial.

    3. Are you aware that hash partitioning pretty much destroys any data maintenance improvements partitioning might provide. And for low volumes of data appropriate indexes might accomplish the same results.

    I suggest you do a 'restart' and flesh out you requirements doc to begin with a complete 'Statement of the Problem' that includes ALL issues that you need to deal with.

    Follow the basic steps I outlined above. Prioritieze those problems/issues. Identify ALL possible solutions: make CERTAIN that your main tables are really normalized properly (if not now is the time to address that). Consider new indexes that might be useful. With 11g you can easily add VIRTUAL columns and index or even partition on them. Consider new join methods you may be familiar with. Are you using BITMAP indexes? Are you using BITMAP-JOIN indexes?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points