Do I Need to Create Indexes on Foreign Keys on Oracle

Do I need to create indexes on foreign keys on Oracle?

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

Does Oracle automatically create a secondary index for FOREIGN KEY columns?

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.

Does a foreign key automatically create an index?

A foreign key is a constraint, a relationship between two tables - that has nothing to do with an index per se.

But it is a known fact that it makes a lot of sense to index all the columns that are part of any foreign key relationship, because through a FK-relationship, you'll often need to lookup a relating table and extract certain rows based on a single value or a range of values.

So it makes good sense to index any columns involved in a FK, but a FK per se is not an index.

Check out Kimberly Tripp's excellent article "When did SQL Server stop putting indexes on Foreign Key columns?".

Postgres and Indexes on Foreign Keys and Primary Keys

PostgreSQL automatically creates indexes on primary keys and unique constraints, but not on the referencing side of foreign key relationships.

When Pg creates an implicit index it will emit a NOTICE-level message that you can see in psql and/or the system logs, so you can see when it happens. Automatically created indexes are visible in \d output for a table, too.

The documentation on unique indexes says:

PostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce uniqueness. Thus, it is not necessary to create an index explicitly for primary key columns.

and the documentation on constraints says:

Since a DELETE of a row from the referenced table or an UPDATE of a
referenced column will require a scan of the referencing table for
rows matching the old value, it is often a good idea to index the
referencing columns. Because this is not always needed, and there are
many choices available on how to index, declaration of a foreign key
constraint does not automatically create an index on the referencing
columns.

Therefore you have to create indexes on foreign-keys yourself if you want them.

Note that if you use primary-foreign-keys, like 2 FK's as a PK in a M-to-N table, you will have an index on the PK and probably don't need to create any extra indexes.

While it's usually a good idea to create an index on (or including) your referencing-side foreign key columns, it isn't required. Each index you add slows DML operations down slightly, so you pay a performance cost on every INSERT, UPDATE or DELETE. If the index is rarely used it may not be worth having.

Oracle SQL : Cannot create a foreign key based on index of more than one column

A Foreign Key references either a Primary Key constraint or a Unique constraint. And it must be a constraint, a unique index is not enough.

If you already have index then you can create unique constraint based on that index. For your case:

alter table test_ek_uk
  add constraint test_ek_uk_nooper_numseq unique (NOOPER, NUMSEQ)
  using index altest_ek_uk_nooper_numseq;

But if you don't have that index - there is no need to create it explicitly.
So, instead of creating the unique index you could create a unique constraint:

alter table test_ek_uk
  add constraint test_ek_uk_nooper_numseq unique (NOOPER, NUMSEQ);

The unique index is created in the background when you create this unique constraint.

Can a foreign key index be part of another index or should it be standalone?

Yes, you do need the index that starts with the foreign key columns. A way to prove this (from Tom Kyte's book Expert One-On-One Oracle) is to do this:

alter table issues disable table lock;

Now if you try to update the parent table like this:

update locations set location_id_fin = <new value>;

you will get this error:

ORA-00069: cannot acquire lock -- table locks disabled for CHILD

However once you add the index that begins with the foreign key columns then you will get the expected error:

ORA-02292: integrity constraint (xxx) violated - child record found

Why oracle doesn't allow creating fk on unique index?

Constraints are part of the SQL standard & relational theory. Indexing is not. It could be part of the standard, but it isn't. But if it were part of the standard then the standard still shouldn't mix up two levels--abstract relation interface vs its implementation that it should be independent of. SQL says a FK (foreign key) references a UNIQUE or PK (primary key). So if the DBMS conforms, you have to do that. But when you declare a UNIQUE constraint the DBMS will give you an implicit unique index. (And when you declare a PK constraint the DBMS will give you an implicit UNIQUE NOT NULL constraint & implicit corresponding index.)

One composite index or many indexes for foreign keys?

Which one is better depends on your actual queries.

One thing to understand is that when you join the table sales once in your query, it will only use one index for it (at the most). So you need to make sure an index is available that is most appropriate for the query.

If you join the sales table always to all three other tables (customer, product and employee) then a composite index is to be preferred, assuming that the engine will actually use it and not perform a table scan.

The order of the fields in the composite index is important when it comes to the order of the results. For instance, if your query is going to group the results by product (first), and then order the details per customer, you could benefit from an index that has the product id first, and the customer id as second.

But it may also be that the engine decides that it is better to start scanning the table sales first and then join in the other three tables using their respective primary key indexes. In that case no index is used that exists on the sales table.

The only way to find out is to get the execution plan of your query and see which indexes will be used when they are defined.

If you only have one query on the sales table, there is no need to have several indexes. But more likely you have several queries which output completely different results, with different field selections, filters, groupings, ...Etc.

In that case you may need several indexes, some of which will serve for one type of query, and others for others. Note that what you propose is not mutually exclusive. You could maybe benefit from several composite indexes, which just have a different order of fields.

Obviously, a multitude of indexes will slow down data changes in those tables, so you need to consider that trade-off as well.

---

Related Topics