• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

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?
0
YBSolutions
Asked:
YBSolutions
  • 4
  • 3
1 Solution
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now