Hi Friends,
Can somebody suggest recommended practice for creating custom indexes on oracle tables ??
Hi Friends,
Can somebody suggest recommended practice for creating custom indexes on oracle tables ??
You mean indexes that are not part of PK or Unique keys .. Or you want to create your own bit map indexes ?? or creating indexes based on user queries (Joins etc)??
You should create indexes in response to sepcific queries that aren't performing well. The more indexes you create the more likely that Oracle is going to use and index and the higher the cost for maintaining the indexes. As a DBA/Database Programmer you need to decide which indexes will help and which indexes won't help.
Thanks,
To Raghud's view, i want to know about indexes based on user's queries.
The best way, IMHO, is to trace a session that is "slow", do a tkprof on it, and see where indexes would be most helpful.
There are no Thumb Rules, that we just follow them as standards. As other person replied, we need to analyze SQl's. If you have SQLs that you are getting from sessions. Just make sure you have indexes on most used SQL joins. If not watch the sessions (V$session,V$SQL etc.,) for day or two . Find the SQL's that are used.
There are so many links on metalink.oracle.com that you can search.
What tables specifically and why? If you're talking about objects owned by SYS or SYSTEM, you just don't do it. That's the best practice.Quote:
Originally posted by aj_usa
Hi Friends,
Can somebody suggest recommended practice for creating custom indexes on oracle tables ??
If you're talking about Oracle "applications" - tables owned by APPLSYS - that's another story. There are some naming conventions that are recommended which help during upgrades. Haven't worked on apps for sometime now..
Quote:
Originally posted by marist89
The best way, IMHO, is to trace a session that is "slow", do a tkprof on it, and see where indexes would be most helpful.
Explain Plan on poor queries is enough to do .. Aint it?
When creating b-tree indexes you need to consider block selectivity e.g. the uniqueness of values within an oracle block. This paper is excellent and contains a script for determining block selectivity.
Not for me.Quote:
Originally posted by abhaysk
Explain Plan on poor queries is enough to do .. Aint it?
Not even explain plan, you can very well say whether the index will be helpful by seeing the query.. (most of the times)Quote:
Originally posted by marist89
Not for me.
I didn't say the query plan wasn't needed, I just don't have all the information I need with EXPLAIN PLAN. To me, a query plan without waits doesn't really tell me much.Quote:
Originally posted by abhaysk
Not even explain plan, you can very well say whether the index will be helpful by seeing the query.. (most of the times)
For example, the query plan tells me the query is using an index. Big deal. There may be 100,000 LIOs, 50,000 recursive calls for TEMP, and 1200 buffer busy waits. That tells me the query plan may be wrong for this statement or my parameters are wrong on the instance.
Is EXPLAIN PLAN good for developers? Sure. However, I don't get involved until the developers can't figure out why their query is slow. That's just my environment.
Quote:
Originally posted by marist89
I didn't say the query plan wasn't needed, I just don't have all the information I need with EXPLAIN PLAN. To me, a query plan without waits doesn't really tell me much.
For example, the query plan tells me the query is using an index. Big deal. There may be 100,000 LIOs, 50,000 recursive calls for TEMP, and 1200 buffer busy waits. That tells me the query plan may be wrong for this statement or my parameters are wrong on the instance.
Is EXPLAIN PLAN good for developers? Sure. However, I don't get involved until the developers can't figure out why their query is slow. That's just my environment.
Well Well Plan will not tell you where your session is waiting on.. but you can suerly comprehend where the problem is ( in that tree structure ) provided you have analyzed your query throughly.. By doing so you can be sure that index used in that plan may worse or better things.. In this context i meant plan is enough to tell you where your problem lies.. not necessary to wait for your sessions to complete and then do a analysis..
Abhay.
Great. That's the way you do it. That's not the way I do it.Quote:
Originally posted by abhaysk
Well Well Plan will not tell you where your session is waiting on.. but you can suerly comprehend where the problem is ( in that tree structure ) provided you have analyzed your query throughly.. By doing so you can be sure that index used in that plan may worse or better things.. In this context i meant plan is enough to tell you where your problem lies.. not necessary to wait for your sessions to complete and then do a analysis..
Abhay.