Menu
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.
498
When you define a foreign key constraint in your database table, an index will not be created automatically on the foreign key columns, as in the PRIMARY KEY constraint situation in which a clustered index will be created automatically when defining it.
Even though Oracle allows you to define a foreign key without an index, it's the SQL workload that determines whether an index is required. A "missing" foreign key index will manifest itself with unnecessary large-table full-table scans against the child table.
Foreign keys do not create indexes. Only alternate key constraints(UNIQUE) and primary key constraints create indexes. This is true in Oracle and SQL Server.
Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.
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.
59
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
