Creating Indexes on Foreign Key Columns!

Debadatta Kar · Oct 30, 2013 5:14AM

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 · Oct 30, 2013 5:22AM

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';

Forum Stats

Quick Links

Categories

Creating Indexes on Foreign Key Columns!

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Creating Indexes on Foreign Key Columns!

Answers