Browse DevX
Sign up for e-mail newsletters from DevX


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




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

First Solution: Detect Unindexed Foreign Key Constraints and Add Indexes to Support Them
After what happened, I wanted to find all constraints that didn't have indexes to support them. Oracle 9i includes the SKIP SCAN index feature that allows the optimizer to choose an index even if the leading column is not part of the query. (Previous versions of Oracle allow you to utilize an index only if the leading column(s) is present in the WHERE clause.) To support an FK constraint on the TransactionID I needed a single index on the TransactionID or composite index, with TransactionID as a leading column.

I wrote the SQL script shown in Listing 1 to perform this task by making an outer join between two inline views. The first view is built by joining two Oracle views with constraint information—User_Constraints and User_Cons_Columns. The second inline view is created from an Oracle view with information from User_Ind_Columns. Then the outer SELECT joins these two inline views on the table name and the comma-delimited string of columns. The condition "Table_name is null" filters out only the constraint rows from the first inline view, which do not have matching index rows from the second view. This fact tells us that indexes with a matching comma-delimited string of columns don't exist, and FK constraints selected by this script were created with no index support.

Here is the output from the SQL script shown in Listing 1:


As you can see, there are two FK constraints with no supporting indexes. One of them is CREDIT_TRANSACTIONID_FK, which is part of the problem described above.

Second Solution: Disable Constraints Temporarily to Speed Up Massive DML
Adding an index to support the FK constraint generally is a good idea. However, each additional index slows the performance of your DML (Data Manipulation Language) statements because the database has to update the index to keep it in sync with the table. In my case, the CREDIT table contains very dynamic data and adding an index was not a desirable solution.

Instead, I decided to disable this specific constraint temporarily to speed up my DELETE statement, and enable it back with the NOVALIDATE clause after DELETE completed. I knew that data integrity would be preserved because I deleted all the child records from the other four tables first. Therefore I could enable the constraint without having to validate the existing data, which lead to a significant performance gain because enabling the constraint with NOVALIDATE was almost instantaneous, whereas doing the same operation without that clause took 25 minutes! Here is the fragment of code, which is utilizing Native Dynamic SQL introduced in Oracle 8i (see my 10-Minute Solution, "New PL/SQL Features in Oracle 8i: Part I") to disable and enable the FK constraint:

... /* Disable the constraint */ vSQLStatement := 'alter table Credit ' || 'modify constraint CREDIT_TRANSACTIONID_FK ' || 'disable'; execute immediate vSQLStatement; delete from Transactions where Period = 23 and OriginType = 'calculated'; /* Enable the constraint */ vSQLStatement := 'alter table Credit ' || 'modify constraint CREDIT_TRANSACTIONID_FK ' || 'enable novalidate'; execute immediate vSQLStatement;

Now the whole procedure took only 12.5 minutes to execute.

Later I encapsulated "enable/disable constraint" code inside the function as a part of my maintenance package. This is good programming practice because it hides the implementation details and makes the code reusable and clean:

/* Disable the constraint */ Maintenance.AlterConstraint('CREDIT', 'CREDIT_TRANSACTIONID_FK', 'DISABLE'); delete from Transactions where Period = 23 and OriginType = 'calculated'; /* Enable the constraint */ Maintenance.AlterConstraint('CREDIT', 'CREDIT_TRANSACTIONID_FK', 'ENABLE NOVALIDATE');

As you can see, FK constraints are a powerful mechanism for enforcing RI rules within a database, and you have to provide it with the necessary supporting indexes to ensure optimal performance.

Boris Milrud has 10 years of experience as a software developer. He is a Senior Database Engineer with Callidus Software, Inc. located in San Jose CA. He is specializing in all aspects of Oracle database software development including database design, programming, optimization and tuning.
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