132

I have a table A and a table B. A has a foreign key to B on B's primary key, B_ID.

For some reason (I know there are legitimate reasons) it is not using an index when I join these two tables on the key.

Do I need to separately create an index on A.B_ID or should the existence of a foreign key provide that?

pmartin8
  • 1,545
  • 1
  • 20
  • 36
aw crud
  • 8,791
  • 19
  • 71
  • 115

7 Answers7

149

The foreign key constraint alone does not provide the index on Oracle - one must (and should) be created.

gabor
  • 1,030
  • 3
  • 12
  • 23
DCookie
  • 42,630
  • 11
  • 83
  • 92
  • 13
    On some databases creating a foreign key constraint creates also an index... i.e. Jet Engine (MSAccess files, Firebird and MySQL) – bubi Apr 23 '15 at 15:19
  • 19
    This answer is meaningless without explicitly referring to a particular database implementation. Sure the question is tagged `oracle` but that isn't immediately obvious when you land here from a google search. – brettwhiteman Aug 18 '16 at 09:25
  • 6
    I can confirm that PostgreSQL - at least at the time of this post - does not do it automatically. – The Dembinski Jan 06 '17 at 21:35
  • Same answer for SQL Server (2016, Azure...) , as far as I know. – Pac0 Oct 16 '18 at 14:14
  • Why must one create an index on a foreign key with Oracle? What are the consequences of not doing so, please? – Bằng Mar 04 '20 at 14:30
  • @Bang: Oracle does not create one for you. You don't *have* to create one, though: things often work just fine if you don't. If table B in the OPs question has just a few rows, an index may not be warranted. See Justin's answer for the big potential consequence. – DCookie Mar 31 '20 at 21:14
  • We need some backing references to be convinced. – M2014 Jan 05 '22 at 15:27
46

Creating a foreign key does not automatically create an index on A.B_ID. So it would generally make sense from a query performance perspective to create a separate index on A.B_ID.

If you ever delete rows in B, you definitely want A.B_ID to be indexed. Otherwise, Oracle will have to do a full table scan on A every time you delete a row from B to make sure that there are no orphaned records (depending on the Oracle version, there may be additional locking implications as well, but those are diminished in more recent Oracle versions).

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • 1
    What about PFK columns? for example if I have an intermediate table for a many-to-many relationship, sholud I create an index for the two PFK columns of this table? – Clamari Jun 27 '18 at 16:05
  • 3
    @Clamari - If C has a primary key of (A_ID, B_ID), the primary key would take care of being able to delete from A. If you also want to be able to delete from B efficiently, you would want an index on `B_ID`. – Justin Cave Jun 27 '18 at 19:05
26

Just for more info: Oracle doesn't create an index automatically (as it does for unique constraints) because (a) it is not required to enforce the constraint, and (b) in some cases you don't need one.

Most of the time, however, you will want to create an index (in fact, in Oracle Apex there's a report of "unindexed foreign keys").

Whenever the application needs to be able to delete a row in the parent table, or update the PK value (which is rarer), the DML will suffer if no index exists, because it will have to lock the entire child table.

A case where I usually choose not to add an index is where the FK is to a "static data" table that defines the domain of a column (e.g. a table of status codes), where updates and deletes on the parent table are never done directly by the application. However, if adding an index on the column gives benefits to important queries in the application, then the index will still be a good idea.

Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
14

SQL Server has never put indexes onto foreign key columns automatically - check out Kim Tripp's excellent blog post on the background and history of this urban myth.

It's usually a good idea to index your foreign key columns, however - so yes, I would recommend making sure each FK column is backed up by an index; not necessarily on that one column alone - maybe it can make sense to create an index on two or three columns with the FK column as the first one in there. Depends on your scenario and your data.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
8

For performance reasons an index should be created. Is used in delete operations on primary table (to check that the record you are deleting is not used) and in joins that usually a foreign key is involved. Only few tables (I do not create them in logs) could be that do not need the index but probably, in this cases probably you don't need the foreign key constraint as well.

BUT

There are some databases that already automatically create indexes on foreign Keys. Jet Engine (Microsoft Access Files) Firebird MySQL

FOR SURE

SQL Server Oracle

DOES NOT

bubi
  • 6,414
  • 3
  • 28
  • 45
1

As with anything relating to performance, it depends on many factors and there is no silve bullet e.g. in a very high activilty environment the maintainance of an index may be unacceptable.

Most salient here would seem to be selectivity: if the values in the index would be highly duplicated then it may give better performance to drop the index (if possible) and allow a table scan.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
0

UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints generate indexes that enforce or "back" the constraint (and are sometimes called backing indexes). PRIMARY KEY constraints generate unique indexes. FOREIGN KEY constraints generate non-unique indexes. UNIQUE constraints generate unique indexes if all the columns are non-nullable, and they generate non-unique indexes if one or more columns are nullable. Therefore, if a column or set of columns has a UNIQUE, PRIMARY KEY, or FOREIGN KEY constraint on it, you do not need to create an index on those columns for performance.

Chinmai
  • 9
  • 1