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.
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.
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..
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.