Creating Indexes on Foreign Key Columns!

Debadatta Kar

Hello All,

We have an application, that is performing poor day by day. From various steps we would take, i need some expert opinion about the indexing strategy.

1. We found none of the foreign keys are indexed. Can we go ahead in creating indexes on all foreign keys columns?

2. Can we put all the existing indexes in monitoring usage mode? Would that give us accurately, which indexes are used? So that we can make them invisible!

Please suggest, if anything else we can do along with the advantages and disadvantages in performing above tasks.

Thanks,

Debadatta Kar

Antonio Navarro

I usually dont index all foreign keys only where I found performance, of course, many people defend create index on all indexes, start from slow query and so on.

You can monitor all indexes (it is not a good idea in big system and can produce locks for the creation). Other way is v$sql_plan, such like;

SELECT sql_id FROM v$sql_plan WHERE object_name ='TAB1_PK';

More on

Index Usage | sql1

HTH - Antonio NAVARRO

sql1.wordpress.com

Hoek

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:292016138754

The Tom Kyte Blog: http://asktom.oracle.com/tkyte/unindex