26

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.

Improve this question
1
  • 2
    No, it doesn't. See this, for example. Commented Feb 13, 2012 at 15:20

1 Answer 1

Reset to default
28

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.