Why we need to create indexes for foreign keys in Oracle

This post is about a small POC I did to check the performance and the effect of indexes on foreign keys in a database. Usually databases like Jet Engine (Microsoft Access Files) Firebird and MySQL automatically creates indexes for foreign keys but in Oracle it is not.

Indexes on foreign keys is designed to avoid child table locking issue as if there is no index on the child table's foreign key, table-level locking may occur when a parent row is updated.

Lets do a quick demo on this.

Setting up the environment

I will be creating a small USERS table as the parent table and a BOOK table as the child table which has foreign key from the USERS table. 

Lets create two tables USERS and BOOKS

CREATE TABLE APS_ESB.USERS (ID NUMBER, INDEX_NO NUMBER, NAME VARCHAR2(30)); CREATE TABLE APS_ESB.BOOKS (ID NUMBER, NAME VARCHAR2(30), USER_ID NUMBER, BOOK_CODE NUMBER);

Populate data to the USERS table

INSERT INTO APS_ESB.USERS (ID, INDEX_NO, NAME) VALUES (1,01,'AMILA1'); INSERT INTO APS_ESB.USERS (ID, INDEX_NO, NAME) VALUES (2,02,'AMILA2'); INSERT INTO APS_ESB.USERS (ID, INDEX_NO, NAME) VALUES (3,03,'AMILA3'); INSERT INTO APS_ESB.USERS (ID, INDEX_NO, NAME) VALUES (4,04,'AMILA4'); INSERT INTO APS_ESB.USERS (ID, INDEX_NO, NAME) VALUES (5,05,'AMILA5');

 Populate data to BOOKS table with large number of records

-- inserting 1000000 rows to the table INSERT INTO APS_ESB.BOOKS SELECT rownum, 'BLAH', 1,  mod(rownum,2)+1 FROM dual CONNECT BY level <= 2000000; COMMIT;

Add primary key and foreign key constraints

ALTER TABLE APS_ESB.USERS ADD PRIMARY KEY (ID); ALTER TABLE APS_ESB.BOOKS ADD CONSTRAINT USERS_FK FOREIGN KEY (USER_ID) REFERENCES APS_ESB.USERS(ID);

Lets play with INDEX
 
Now there are no any indexes created so far. Lets try to delete data from the very small USERS (parent) table.

SET AUTOTRACE ON; and collect the statistics to compare. 

DELETE APS_ESB.USERS WHERE ID=3; // query to delete a row from users.

Execution Plan ---------------------------------------------------------- Plan hash value: 880004690 ---------------------------------------------------------------------------------- | Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------- |   0 | DELETE STATEMENT   |             |     1 |    13 |     0   (0)| 00:00:01 | |   1 |  DELETE            | USERS       |       |       |            |          | |*  2 |   INDEX UNIQUE SCAN| SYS_C008544 |     1 |    13 |     0   (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------   2 - access("ID"=3) Statistics ----------------------------------------------------------     36  recursive calls      9  db block gets     2981  consistent gets      0  physical reads    636  redo size    839  bytes sent via SQL*Net to client    783  bytes received via SQL*Net from client      3  SQL*Net roundtrips to/from client      3  sorts (memory)      0  sorts (disk)      1  rows processed

Now lets create a INDEX to the foreign key in child table and check the statistics again.

CREATE INDEX BOOKS_IDX ON BOOKS(USER_ID); // create an index

Execution Plan ---------------------------------------------------------- Plan hash value: 880004690 ---------------------------------------------------------------------------------- | Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------- |   0 | DELETE STATEMENT   |             |     1 |    13 |     0   (0)| 00:00:01 | |   1 |  DELETE            | USERS       |       |       |            |          | |*  2 |   INDEX UNIQUE SCAN| SYS_C008544 |     1 |    13 |     0   (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------   2 - access("ID"=3) Statistics ----------------------------------------------------------      1  recursive calls      6  db block gets      1  consistent gets      2  physical reads    516  redo size    839  bytes sent via SQL*Net to client    783  bytes received via SQL*Net from client      3  SQL*Net roundtrips to/from client      1  sorts (memory)      0  sorts (disk)      1  rows processed

	consistent gets - Number of times a consistent read was requested for a block.
Without INDEX	2981
With INDEX	6

Explanation 

 

We can only successfully delete a row in parent table if there are no corresponding FKs based on this parent row. When there is no associated index, Oracle perform this check on the large child table by performing an expensive, slow, Full Table Scan (FTS) because Oracle needs to ensure while it runs the FTS looking for any FKs associated with the deleted parent row.

When we create a INDEX , we can see a drastic reduction in the consistent gets from 2981 to 6. This means now Oracle does not perform any expensive FTS of the child table so the child table locking issues will not occur.