Browse DevX
Sign up for e-mail newsletters from DevX


Don't Let Referential Integrity Degrade Your Database Performance-2 : Page 2




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Reasons for Foreign Key Constraints
There are many reasons for creating an index that supports the foreign key (FK) constraint. The first and the most obvious one is that the FK constraint represents a relation between tables, and if these tables are part of a SQL query, there's a 99.9 percent chance that they will be joined on the constraint column. In this case you definitely need an index that supports SQL queries.

Another reason to have indexes for FK constraints is to avoid locking conflicts during an update of the child table. Oracle provides a row-level locking mechanism that removes many locking problems contained in databases with page-level and block-level locking. However, even row-level locking won't help if frequent updates to the child table cause it to lock the entire parent table just because the supporting index is missing.

There is also a less-obvious problem that occurs with massive DELETE operations on the parent table. This is dangerous because, unlike the SELECT command, it triggers SQL queries implicitly in the background and you could spend hours and days trying to figure out why your simple DELETE command is unable to complete.

Problem with Massive DELETEs on the Parent Table
Recently I had to solve a problem with a procedure that was written to delete data from a set of related tables for a specified time period in order to prepare the tables for the next run. There were five tables involved: one transactions table (the parent) and four child tables—Credits, Deposits, Incentives, and Payments. All four tables were in one-to-many relationship with the Transactions table, which had a PK based on the TransactionID column. All child tables also included a TransactionID column and a foreign key that referenced the PK of the Transactions table.

As you can see, declarative RI would not allow me to delete any records in the Transactions table. So a procedure was written in such a way that it deleted records in four child tables for the specified time period and only after that deleted records from the parent table for the same time period. It worked just fine for a while, but then it started running really slow (1216 hours) on big data sets. In my case, I was trying to delete 10,000 transactions from a table with 200,000 transactions total. Here were the stats for the other tables:

  • Credit: 3 million records to delete, 25 million total
  • Deposits: 100,000 records to delete, 1.5 million total
  • Incentives: 150,000 records to delete, 2.5 million total
  • Payments: 75,000 records to delete, 2.5 million total
First I tried to locate the particular DELETE statement that was the source of the problem. So I put debugging messages in the code and recompiled and ran the procedure. To my surprise, during the first 15 minutes the procedure completed the deletion of records in all child tables and started executing the last DELETE statement, which deleted data from the Transactions table. I sat there for 6 hours (!) before I lost my patience and killed it. The last statement had the following, very simple syntax:

delete from Transactions where Period = 23 and OriginType = 'calculated';

I checked on all available indexes of the Transactions table and, sure enough, the composite index on Period and OriginType was there. I checked the execution plan for the corresponding SELECT statement:

select * from Transactions where Period = 23 and OriginType = 'calculated';

The execution plan was correct: it was using that index. SELECTing count(*) with the same WHERE clause gave me the correct result (10,000) in less than one second. OK, so what was the next step?

What's the difference between SELECT and DELETE with the same WHERE clause? The answer is that DELETE triggers RI rules, so the database has to issue some SQL commands in the background to enforce those rules. I now had to find which SQL statements were being executed.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date