Oracle Hash Partitioning.

Business Objects
Data analysis
Data warehousing applications
Microsoft Windows
Hi, In Oracle hash paritioning do we have control over hash value based on which partitioning is done? My issue is - We have to make a design for datawarehouse application and in the landing area we are planning for range-hash partitionining based on the keys (and their volumes). Now, if we use range-hash paritioning, if the hash values of those keys which has the large number of records turn out to be the same. then I may face problem, becuase one partition will have large number of records, whereas the other partitions would be hardly occupied (wasting resources). Currently we do not have the volume statistics per key values, so do we have control over the hash partition values so that I can manually define partition based on hash value? As an alternate approach, can we do paritioning dynamically based on the volumes when the data is loaded in the landing area? Regards, Rohit.

Answer Wiki

Thanks. We'll let you know when a new response is added.

The point of hash partitioning is: let the DB manage the partitioning automatically. Therefore I do not think you can control it.

If you want to control partitions within each range, you could use range-list partitioning, as long as you are working at least with Oracle 9iR2, which is recommendable for data warehousing, anyway.

Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Techanalyst10
    Hi pelaez, Thanks for the response. But list partitioning may not be useful in my case. The reason is, there are around 180 distinct (partiton) key values and the volume of data is high for some of the key values and low for the other key values. Hence, if I keep the list partitioning, the number of partitions would be more. Also, for some of the partitions, tablespaces may get wasted due to the volume of data (based on key value) would be less and if I keep the tablespace as minimum with autoextend ON, my performance would degrade increasing the tablespace size while data loading. I read that in HASH clustering there is an option to use your own HASH funtion. Do you know, any similar workaround in HASH partitioning. Thanks and Regards, Rohit.
    0 pointsBadges:
  • Pelaez
    Considering all you have said, probably neither range-hash nor range-list is a good solution for your DW. I would try to A) Identifiy another candidate key for hash- or list-partitioning. B) If not possible, you might try to increase the number of partitions decreasing the range (e.g.: if partitioning by year, repartitioning by month).
    0 pointsBadges:
  • Oracle Hash Partitioning (Q/A) | Seek The Sun Slowly
    [...] Address: (0) Comments Read [...]
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: