Solved

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

Posted on 2012-03-18
8
347 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
  • 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 76

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
 

Author Comment

by:YBSolutions
ID: 37751396
I think we can use dbms_advisor too?
Any thought or example you can share, please?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

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 76

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
export Oracle diagram from Oracle DB including VIEWS 8 100
VB.Net - CSV to Oracle table 4 51
sort a spool into file output in oracle 1 22
use lov values 2 18
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

919 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now