No. of index in a table

[oracle8]

hi!

Any ideas on what is the rule of thumb in how many index should a table have. I'm using oracle 8.05 in Windows NT.

Thanks

Rommel

 

[SantaMufasa]

Oracle8,

Here are some rules of thumb on Oracle indexes:

1) You don't need even one index on tables that occupy two database blocks or less since a full table scan on a two-block table will be faster than access via an index.

Barring Rule #1 above, you probably benefit from indexes for:
2) Each Primary Key
3) Each Foreign Key
4) Operands in WHERE equi-joins (WHERE a = b)

Oracle conventional wisdom typically discourages use of indexes when a query returns more than 10% of the rows of a table.

There are many additional rules of thumb regarding performance and indexes, but you should probably respond to these suggestions before we overwhelm you with more rules thay might not apply to your situation. Can you disclose more characteristics about your application environment as they relate to possible index usage?

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:56 (05Dec03) GMT, 18:56 (04Dec03) Mountain Time)

 

[sbix]

Another general rule is:
Run frequently the ANALYZE blah blah blah COMPUTE STATISTICS on all objects heavely modified (including indexes).
After a massive INSERT or UPDATE it's advisable to execute an ALTER INDEX blah blah REBUILD.