Home » RDBMS Server » Server Administration » Hash Partioning - Performance Improvement
Hash Partioning - Performance Improvement [message #170328] Wed, 03 May 2006 06:13 Go to next message
rkulasek
Messages: 15
Registered: April 2006
Junior Member
Hi

I am trying to introduce Hash Partitioning in our product. I have read through quite a bit of documentation. But I am still with left with a bunch of questions. Will be a great help if you can help me figure these things out
We are using Oracle 10g on HP-UX 11i and Solari 9

1) Is hash partitioning useful only when the partitions are spread across tablespaces in separate physical disks? What happens if I put all my hash partitions into same tablespace in one physical disk?

2) When creating hash partitions, I read that I should use some unique key as the partition key. This will ensure that data is evenly distributed across partitions.
Now, when I query the table, will I see any performance improvement in the query if I use only some of the partition key columns instead of querying by all the partition key columns?

3) Does partition pruning happen in hash partitioning? If not, how is the query performance acheieved by Oracle?

4) I tried hash partitioning a table. I put all the partitions in the same tablespace. The table had a total of 18 million rows. However, I found that there is almost no difference in query time whether I query from this hash-partitioned table or from a non-partitioned table (with same data)
Why is this?

Thanks much. Your responses will be a great help
Raja
Re: Hash Partioning - Performance Improvement [message #170522 is a reply to message #170328] Thu, 04 May 2006 04:22 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
hash partitioning is meant , more for administrative reasons, and less for performance improvement...for best performance improvoment , if possible you can try to do range partitioning ,and see whether partition elimination is taking place.

partition elimination is , afaik, may not happen that often in hash partitioning, because , the rows are distributed via a hash function and not based on any range.

Re: Hash Partioning - Performance Improvement [message #208646 is a reply to message #170522] Mon, 11 December 2006 12:37 Go to previous messageGo to next message
anjanroy
Messages: 1
Registered: December 2006
Junior Member
I am trying to hash partition a table based on the source_system field. There are 4 sources that populate the table and I create 4 partitions on the table on source_system field. However, after I load the table, I see only 2 partitions are populated.

My understanding is that if I use a hash function, all the 4 different values of the source_system field will go to different partitions - but somehow that is not happening.

Any ideas?
Re: Hash Partioning - Performance Improvement [message #233628 is a reply to message #208646] Fri, 27 April 2007 00:32 Go to previous messageGo to next message
beetel
Messages: 96
Registered: April 2007
Member
I would like to read more about this thread. MOst questions are left unanswered. I am sure there are lots of experts in this forum that can give their much-sought insights.. Experts?? can you please spare us some time???? thanks..
Re: Hash Partioning - Performance Improvement [message #234100 is a reply to message #170328] Mon, 30 April 2007 00:27 Go to previous message
rkulasek
Messages: 15
Registered: April 2006
Junior Member
Hi

Just a follow up from my side.
I am giving below some information whatever I ended up implementing in our product.
I am giving this hoping this might help anyone trying to implement partitioning.

===========

We had to give up hash partitioning since the hash partitioning performance turned out to be worse than a non-partitioned table.
We partitioned only those tables which "lent" themselves to range partitioning.
And partitioning an existing table in a production system involves downtime. This downtime and the space required for such partitioning are unpredictable.
So we decided we will support partitioning only during a fresh install of the database.
Previous Topic: Svrmgrl is lost
Next Topic: How to enquire Oracle Inventory
Goto Forum:
  


Current Time: Wed Dec 07 22:46:17 CST 2016

Total time taken to generate the page: 0.15925 seconds