-
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)??
Raghu
-
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.
Jeff Hunter
-
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.
Raghu
-
Re: recommended practice for creating custom indexes on oracle tables
Originally posted by aj_usa
Hi Friends,
Can somebody suggest recommended practice for creating custom indexes on oracle tables ??
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.
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..
-
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?
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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.
-
Originally posted by abhaysk
Explain Plan on poor queries is enough to do .. Aint it?
Not for me.
Jeff Hunter
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|