21

I'm currenly developing on Oracle. I have several tables for which I defined FOREIGN KEY constraints. I have already read this SQL Server-oriented and this MySQL-oriented questions but I could find none about Oracle.

So the question is always the same: in order to optimize query performance, for those columns for which I create a FOREIGN KEY constraint, do I also have to create an explicit secondary index? Doesn't Oracle automatically create an index on FOREIGN KEYed columns to boost performances during JOINs?

I usually perform queries in which the WHERE clause compare against those columns.

Community
  • 1
  • 1
usr-local-ΕΨΗΕΛΩΝ
  • 26,101
  • 30
  • 154
  • 305
  • 2
    No, it doesn't. See [this](http://stackoverflow.com/questions/4127206/do-i-need-to-create-indexes-on-foreign-keys), for example. – Alex Poole Feb 13 '12 at 15:20

1 Answers1

24

No, Oracle doesn't automatically create indexes on foreign key columns, even though in 99% of cases you probably should. Apart from helping with queries, the index also improves the performance of delete statements on the parent table.

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • 2
    Such indexes have a cost to maintain, though, and there are times when it's best not to create them. Personally I suspect it should create them by default and let you drop them if you want, but Oracle is far from the only DB to behave as it does. PostgreSQL doesn't automatically create indexes for the referencing side of a foreign key relationship either. – Craig Ringer Aug 23 '12 at 04:51