| Hash Partioning - Performance Improvement [message #170328] |
Wed, 03 May 2006 06:13  |
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  |
orausern
Messages: 826
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 |
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 #234100 is a reply to message #170328] |
Mon, 30 April 2007 00:27 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
