Solved

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

Posted on 2012-03-18
8
358 Views
Last Modified: 2012-04-03
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
Comment
Question by:YBSolutions
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 5

Expert Comment

by:Bajwa
ID: 37735369
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
 

Author Comment

by:YBSolutions
ID: 37735380
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37738031
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:YBSolutions
ID: 37751396
I think we can use dbms_advisor too?
Any thought or example you can share, please?
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 37752192
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
 

Author Comment

by:YBSolutions
ID: 37753270
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37753303
>>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
 

Author Closing Comment

by:YBSolutions
ID: 37803196
I do apologize for delay.
Thanks for providing the solution.
It really work.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question