How to determine if hash partitioned index is excellent choice or not?

We have 4 instances RAC on 11.1.0.7 and ASM.
We have multiple tables having billions of records and need to decide if hash partitioned index is best choice or other index type is best.

Is there any already proved method to decide the same?
YBSolutionsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BajwaCommented:
If you do not have or do not know a partitioning key then hash partition index will be best.  It basically tells oracle to divided the data in equal parts as deem fit.

1. Are you trying to query the data and thus you need index?
2. Are the tables partitioned or not?

What is the end objective.
0
YBSolutionsAuthor Commented:
We have different kind of tables those need to be partitioned like.
1. Tables having heavy insert and update (about 30 transactions per sec.).
2. Tables having heavy select.
3. Tables having heavy insert, update and select.

End objectives are
1. To remove the bottleneck.
2.  Achieve optimum level of performance.
0
slightwv (䄆 Netminder) Commented:
A partition plan needs to be based on the data.  We really cannot help you with this since we do not know your data.

I suggest you read about the different ways to partition and when and when not to use variaous options in the docs.  Then take a look at your data and how it will be accessed.

http://docs.oracle.com/cd/E11882_01/server.112/e25789/schemaob.htm#CFAGCECI
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

YBSolutionsAuthor Commented:
I think we can use dbms_advisor too?
Any thought or example you can share, please?
0
slightwv (䄆 Netminder) Commented:
Looks like you might be able to:
http://www.oracle-base.com/articles/11g/SQLAccessAdvisor_11gR1.php

1.The advisor now includes advice on partitioning schemes that may improve performance
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
YBSolutionsAuthor Commented:
Thanks a lot for it is really helpful.

May I know how much reliable these suggestions are?
Do you have some case studies those could prove it?
0
slightwv (䄆 Netminder) Commented:
>>May I know how much reliable these suggestions are?

You need to see if the suggestions make sense.  they are based on the information available to the tool at the time.  If the V$ tables are accurate at the time then the suggestions should be sound.

If what is available to the tool isn't how the database normally runs then the suggestions will likely not be correct.

>>Do you have some case studies those could prove it?

No.  There is no magic wand to tuning.  There is no substitition to knowing your database and how it is used.  Only then can you be confident on the choises made.
0
YBSolutionsAuthor Commented:
I do apologize for delay.
Thanks for providing the solution.
It really work.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.