Partioning is a great way to reduce index maintenance. It might even help the performance of certain types of queries. However, and I've not done it in Oracle, I can't see how "hash partitioning" will help anything because of the things I mentioned. It's about as effective as partitioning on NEWID() or any other random number.
[EDIT] The only place I can see where it might help is to spread the load across drives as Gail just mentioned. What would be a much larger help is to fix the data and give it some sort of reasonable partitioning column.
Consider the following example... we have some very easily sortable data that, under normal conditions, would appear in a single partition (say, the "A" partition out of 26 lettered partitions).
SELECT HASHBYTES('SHA1','AAAAA') UNION ALL
SELECT HASHBYTES('SHA1','AABAA') UNION ALL
SELECT HASHBYTES('SHA1','AACAA') UNION ALL
SELECT HASHBYTES('SHA1','AADAA') UNION ALL
Now, if we look at the HASHBYTES output of that, you'll easilly see that having similar data means nothing to HASHBYTES.
There are 5 items and the hashbytes all start with a different character. How is this going to help anything by partitioning? All 5 partitions got updates. That means that 5 indexes also got updated and you still have to go across all 5 partitions to return the 5 pieces of data that start with "AA".
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Helpful Links:
How to post code problemsHow to post performance problemsForum FAQs