Oracle-DB: Requirements for a multi-column index for protecting foreign key constraints

 If you want avoid blocking locks while using referential integrity constraints in an Oracle-DB, it is suggested to protect a foreign key constraint by an index.

Not always it is useful and necessary to protect a foreign key constraint with an index. But this question was considered separately in this blog post.

The question today is:

What column structure must an index have to be accepted by the Oracle DB as protection for a multi-column foreign key constraint?

To answer this question, several scenarios are tested.

Test setup:

CREATE TABLE Dim(ID1 NUMBER, ID2 NUMBER, ID3 NUMBER, Name VARCHAR2(200));

ALTER TABLE Dim ADD CONSTRAINT Dim_PK PRIMARY KEY (ID1, ID2, ID3);

INSERT INTO Dim VALUES(1,1,1, 'First');

INSERT INTO Dim VALUES(2,2,2, 'Second');

CREATE TABLE Fact(ID1 NUMBER, ID2 NUMBER, ID3 NUMBER, Name VARCHAR2(200));

ALTER TABLE Fact ADD CONSTRAINT Fact_Dim_FK FOREIGN KEY (ID1, ID2, ID3) REFERENCES Dim(ID1, ID2, ID3);

Scenario 1: No index

second session is blocked by the first session
-- Session 1
INSERT INTO Fact VALUES (1,1,1, 'Erster');

-- Session 2
UPDATE Dim SET ID3=3 WHERE ID1=2 AND ID2=2 AND ID3=2;

Scenario 2: Incomplete index

second session is blocked by the first session 
CREATE INDEX Fact_IX ON Fact(ID1);

-- Session 1
INSERT INTO Fact VALUES (1,1,1, 'Erster');

-- Session 2
UPDATE Dim SET ID3=3 WHERE ID1=2 AND ID2=2 AND ID3=2;

Scenario 3: Complete index

second session is not blocked by the first session 
DROP INDEX Fact_IX;
CREATE INDEX Fact_IX ON Fact(ID1, ID2, ID3);

-- Session 1
INSERT INTO Fact VALUES (1,1,1, 'Erster');

-- Session 2
UPDATE Dim SET ID3=3 WHERE ID1=2 AND ID2=2 AND ID3=2;

Scenario 4: Complete index but different order

second session is not blocked by the first session 
DROP INDEX Fact_IX;
CREATE INDEX Fact_IX ON Fact(ID3, ID1, ID2);

-- Session 1
INSERT INTO Fact VALUES (1,1,1, 'Erster');

-- Session 2
UPDATE Dim SET ID3=3 WHERE ID1=2 AND ID2=2 AND ID3=2;

Scenario 5: Complete index but additional columns before constraint columns

second session is blocked by the first session 
DROP INDEX Fact_IX;
CREATE INDEX Fact_IX ON Fact(Name, ID3, ID1, ID2);

-- Session 1
INSERT INTO Fact VALUES (1,1,1, 'Erster');

-- Session 2
UPDATE Dim SET ID3=3 WHERE ID1=2 AND ID2=2 AND ID3=2;

Scenario 6: Complete index but additional columns between constraint columns 

second session is blocked by the first session 
DROP INDEX Fact_IX;
CREATE INDEX Fact_IX ON Fact(ID1, Name, ID3, ID2);

-- Session 1
INSERT INTO Fact VALUES (1,1,1, 'Erster');

-- Session 2
UPDATE Dim SET ID3=3 WHERE ID1=2 AND ID2=2 AND ID3=2;

Scenario 7: Complete index but additional columns after constraint columns

second session is not blocked by the first session 
DROP INDEX Fact_IX;
CREATE INDEX Fact_IX ON Fact(ID3, ID1, ID2, Name);

-- Session 1
INSERT INTO Fact VALUES (1,1,1, 'Erster');

-- Session 2
UPDATE Dim SET ID3=3 WHERE ID1=2 AND ID2=2 AND ID3=2;

Conclusion:

To be accepted as protection for a foreign key constraint, an index must contain all columns of the foreign key constraint as the first columns of that index, no matter which order they are in.

Comments

Post a Comment

Popular posts from this blog

Common pitfalls using SQL*Net via Firewalls

It's very common that firewalls are terminating idle TCP sessions after a limited time, often after approximately one hour. Possible problem If you have a long running SQL or PL/SQL program and the firewall terminates your apparently idle TCP session this may lead to the following scenario: the database is not able to send result to client. After reaching the TCP-timeout the database server terminates the DB-Session the client program stays in socket read forever waiting for the database response Oracle's full solution: SQLNET.EXPIRE_TIME=x in sqlnet.ora Oracle's solution for this problem is setting parameter SQLNET.EXPIRE_TIME=x in sqlnet.ora where x are the minutes between keep alive packets on idle sessions that inform the firewall that this session is always alive. SQLNET.EXPIRE_TIME hast to be set in the sqlnet.ora of your RDBMS' ORACLE_HOME. Normally $ORACLE_HOME/network/admin. If you have a grid infrastructure setting SQLNET.EXPIRE_TIME in sqlnet
Read more

Oracle-DB: How to check for appropriate sequence caching

Image
A common pitfall while using sequences in Oracle-DB is forgetting to define sequence caching. The default for sequences is cache size = 0. That means, every sequence.nextval operation triggers a single write operation on dictionary table sys.SEQ$. Especially if frequently calling sequence.nextval uncached sequences may cause significant performance degradation. Frequently calling sequence.nextval in parallel sessions may lead to ramdom lock scenarios in library cache. Setting a cache size for a sequence may heavy increase performance of nextval-operations. There's a minimal memory overhead for caching a sequence because all that's needed is a counter to increase in SGA memory and a upper limit. If this limit is reached with the counter, the upper limit is increased by cache size and the new upper limit is written down once to SEQ$. The possible drawback for sequence caching is that you loose all your values between the current value and the highest cached value if yo
Read more

Oracle DB: Evaluate database audit trail with Panorama

Image
The performance analysis tool Panorama for Oracle now supports both evaluation of standard audit trail as well as evaluation of unified audit trail. The menu item "DBA General" / "Audit Trail" / "Auditing rules" shows the current configuration of the DB audit trail as well as several auditing rules for standard, fine grained and unified audit trail. With menu item "DBA General" / "Audit Trail" / "Standard audit trail" you open a dialog for evaluation of standard audit trail including find grained audit trail. Depending on the choosen value for "Grouping" single audit records are show for the selected period or the result is grouped by time with record counts for the top x OS and DB users, machines and actions. By clicking at the links in the columns for "Action", "Machine", "User" etc. you can refine the grouped result. Clicking at the column "Audits total" shows the s
Read more