Oracle Hash Partitioning.

Tags:
Business Objects
Data analysis
Data warehousing applications
Database
DB2
Oracle
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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • 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:
    report
  • 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:
    report
  • Techanalyst10
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/oracle-hash-partitioning/ (0) Comments Read [...]
    0 pointsBadges:
    report

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following