Clarify myths of indexing foreign key constraints

If you use foreign keys there’s one very common dogma:
You always need to index all the column(s) for which you define a foreign key constraint!

I definitely do not agree with this dogma and want to explain why.

There are two reasons for indexing columns with foreign key constraints:

1. Avoiding full table scans on referencing table during deletes on referenced tables
2. Avoiding blocking lock problems e.g. if you are doing DML on both referencing and referenced table

Considerations on full table scan due to foreign key constraint not protected by index

• If you do not index referencing column(s) than delete on referenced table leads to a full table scan on referencing table for every deleted row
• Therefore you should index referencing column(s) especially in OLTP-environments if your referenced table has more than a small amount of records and there are DELETE-Operations on this referenced table.
• Otherwise you really needn’t index the referencing column(s) if your referenced table  has only few records
• In this case:
  • rare cases of full table scan due to delete on referenced table can be accepted
  • an index would be very little selective and useless for data access, but costs storage and maintenance effort during DML

Lock-behavior with and without indexed foreign key column(s)

Let’s consider two tables with foreign key constraint on Oracle 11.2:

CREATE TABLE Fact (ID Number, Name VARCHAR2(100), 
CONSTRAINT Fact_PK PRIMARY KEY (ID)
);

CREATE TABLE Dim (ID NUMBER, Name VARCHAR2(100), Fact_ID NUMBER,
CONSTRAINT Dim_PK PRIMARY KEY (ID),
CONSTRAINT Fact_FK FOREIGN KEY (Fact_ID) REFERENCES Fact(ID)
);

There is no index at Dim(Fact_ID) for next tests.

Szenario 1: Pending insert-DML on Fact + insert into Fact in concurrent session without index on Dim(Fact_ID)

-- Session 1:
INSERT INTO Fact VALUES (1, 'First');
INSERT INTO Fact VALUES (2, 'Second');
COMMIT;
INSERT INTO Fact VALUES (3, 'Third');

There are row-exclusive locks (mode 3) with lock-type TM on both Fact and Dim for session 1.

-- Session 2:
INSERT INTO Fact VALUES (4, 'Third');

There are additional row-exclusive locks (mode 3) with lock-type TM on both Fact and Dim for session 2 but nothing blocks.

Szenario 2: Pending insert-DML on Fact + update on Fact without index on Dim(Fact_ID)

-- Session 1:
INSERT INTO Fact VALUES (1, 'First');
INSERT INTO Fact VALUES (2, 'Second');
COMMIT;
INSERT INTO Fact VALUES (3, 'Third');

There are row-exclusive locks (mode 3) with lock-type TM on both Fact and Dim for session 1.

-- Session 2:
UPDATE Fact SET Name = 'Firster' WHERE ID=1;

There is an additional row-exclusive lock (mode 3) with lock-type TM on Fact for session 2 but nothing blocks.

-- Session 2:
UPDATE Fact SET ID=1, Name = 'Firster' WHERE ID=1;

Now including the PK-column in the update this operation is blocked by session 1.
This will happen because requested shared lock (mode 4) on Dim is blocked by exclusive lock from session 1.

Szenario 2: Same again, but with index on Dim(Fact_ID)

CREATE INDEX Protect_FK ON Dim(Fact_ID);

Now with index updating the PK-column by session 2 is possible.

Szenario 3: Pending update on Dim + insert on Dim in concurrent session without index on Dim(Fact_ID)

-- Session 1:
INSERT INTO Fact VALUES (1, 'First');
INSERT INTO Fact VALUES (2, 'Second');
COMMIT;
UPDATE Fact Set ID=1, Name = 'FIRSTER' WHERE ID=1;

There is an row-exclusive lock (mode 3) with lock-type TM on Fact for session 1.

-- Session 2:
INSERT INTO Fact VALUES (4, 'Fourth');

There are additional row-exclusive locks (mode 3) with lock-type TM on both Fact and Dim for session 2 but nothing blocks.

Szenario 4: Pending insert-DML on Fact + insert on Dim without index on Dim(Fact_ID)

-- Session 1:
INSERT INTO Fact VALUES (1, 'First');
INSERT INTO Fact VALUES (2, 'Second');
COMMIT;
INSERT INTO Fact VALUES (3, 'Third');

There are row-exclusive locks (mode 3) with lock-type TM on both Fact and Dim for session 1.

-- Session 2:
INSERT INTO Dim VALUES (1, 'First', 1);

There are additional row-exclusive locks (mode 3) with lock-type TM on both Fact and Dim for session 2 but nothing blocks.

Szenario 5: Pending update on Fact + insert on Dim without index on Dim(Fact_ID)

-- Session 1:
INSERT INTO Fact VALUES (1, 'First');
INSERT INTO Fact VALUES (2, 'Second');
COMMIT;
UPDATE Fact SET ID=2, Name='More' WHERE ID=2;

There is an row-exclusive lock (mode 3) with lock-type TM on Fact for session 1.

-- Session 2
INSERT INTO Dim VALUES (1, 'First', 2);

There are additional row-exclusive locks (mode 3) with lock-type TM on both Fact and Dim for session 2 but nothing blocks.
In this case it doesn’t matter that PK-column is updated because shared lock on Dim is only held shortly by session 1 during update.

Szenario 6: Concurrent DML on Dim with same Fact_ID without index on Dim(Fact_ID)

-- Session 1:
INSERT INTO Fact VALUES (1, 'First');
INSERT INTO Fact VALUES (2, 'Second');
INSERT INTO Dim VALUES (1, 'First', 1);
INSERT INTO Dim VALUES (2, 'Second', 1);
COMMIT;
UPDATE Dim SET Fact_ID=1, Name='More' WHERE ID=1;

-- Session 2:
UPDATE Dim SET Fact_ID=1, Name='More' WHERE ID=2;
INSERT INTO Dim VALUES (3, 'Hugo', 1);

-- Session 1:
DELETE FROM Dim WHERE ID=1;

Both sessions have row-exclusive locks (mode 3) with lock-type TM on both Fact and Dim but nothing blocks.

Szenario 7: Update on Dim + Delete an not referenced record on Fact without Index on Dim(Fact_ID)

-- Session 1:
INSERT INTO Fact VALUES (1, 'First');
INSERT INTO Fact VALUES (2, 'Second');
INSERT INTO Dim VALUES (1, 'First', 1);
INSERT INTO Dim VALUES (2, 'Second', 1);
COMMIT;
UPDATE Dim SET Fact_ID=1, Name='More' WHERE ID=2;

-- Session 2:
DELETE FROM Fact WHERE ID=2;

Session 1 has  row-exclusive locks (mode 3) with lock-type TM on both Fact and Dim.
Session 2 has row-exclusive lock (mode 3) on Fact and is blocked requesting shared lock (mode 4) on Dim.

Szenario 8: Same again, but with index on Dim(Fact_ID)

CREATE INDEX Protect_FK ON Dim(Fact_ID);

Now with index on Dim(Fact_ID) delete on Fact with pending update on Dim is possible.

Conclusion

1.

• There are real world systems following the above dogma with more than 50% of storage footprint caused only by useless indexes to protect all referential integrity constraints.
• By carefully rating the necessity of this indexes you have a large lever to reduce storage footprint and effort for index maintenance.

2.

• Primary key columns should be treated as technical row identities and their values should never be changed.
• This enables you to skip this primary key columns in the set-clause of update-statements.
• This way updates on referenced tables are now possible without influence on locking behavior via foreign key constraints even if foreign key column is not indexed.
• Verify that your ORM-frameworks supports skipping primary key columns from update.

3.

• Deletes on referenced tables need shared lock on referencing table if FK is not protected by index (same behavior like update including PK-column).
• Updates without PK-columns in set-clause and inserts on referenced tables are not blocked by DML on referencing table even if foreign key is not protected by index.

Summary:

• If you don’t have frequent DML on referenced table there is usually no need to protect a foreign key constraint with an index

p.s.:
You can scan your database for possibly unnecessary indexes with Panorama’s selects at menu “Spec. additions / Dragnet investigation” point 1.2.

If you don’t agree with this perspective on indexing foreign keys please share your arguments.

 

 

 

Panorama: Save request parameter to recall page with specific filters at later time

Sometimes in Panorama you have drilled down in analysis from step to step, cumulated several filter conditions and found what you are looking for.

This finding you want to share with colleagues or store for later use.
Panorama allows you at several pages to copy your current request parameter to clipboard and use ist later to reconstruct your current page.

Saving request parameters:

Choose “Copy request parameters to clipboard” from small menu at left corner of headline (if it is available for this function).

You can use parameters from clipboard now to store or mail or …

Restore a page with given parameters:

Call menu “Spec. additions / Execute with given parameters” and paste your stored parameters in text area. Click execute to restore your page with this parameters.