Solved

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

Posted on 2012-03-18
8
351 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 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
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.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Consolidating oracle query results to a single line 8 65
Queries 15 48
Row_number in SQL 6 34
why truncate is faster than delete in oracle ? 4 28
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…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

828 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