Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Don't Let Referential Integrity Degrade Your Database Performance

Foreign key (FK) constraints without supporting indexes can cause severe performance problems during SQL queries and massive DML (Data Manipulation Language) operations, such as DELETEs or UPDATEs. Use a SQL script to detect unindexed foreign key constraints.


advertisement
racle databases provide a powerful mechanism for enforcing business rules, called referential integrity (RI), which is implemented either via a declaration of foreign key (FK) constraints ("declarative RI") or via triggers. It is almost impossible to find an Oracle database with no declarative RI implemented.

Declarative RI protects the integrity of related data by defining the relation between data in different tables. Relation between two tables is defined using a combination of primary, or unique, key (PK) and FK constraints. A PK constraint uniquely identifies a row in a database table, while an FK constraint actually declares the rules that will be applied to the related data. Declarative RI rules basically dictate that FK values in a column, or set of columns, of one table match the PK or unique constraint values in a related table.

In the SCOTT schema provided with Oracle's sample database we could illustrate the declarative RI using the sample EMP table, which contains employee information, and DEPT table (department information). DEPT and EMP are in a one-to-many relationship: one department (parent) could contain multiple employees (children). Each employee belongs to a certain department, so each DeptNo value in the EMP table (FK) has to exist as a DeptNo value in the DEPT table (PK). You don't need to write any PL/SQL code to enforce this rule; all you need to do is DECLARE an FK constraint using the following command:



alter table EMP add constraint EMP_DEPTNO_FK foreign key (DEPTNO) references DEPT(DEPTNO);

That's why it's called "declarative RI." It assures that all of the references within the database are valid. In the case of the EMP and DEPT tables, the EMP_DEPT_FK FK constraint makes sure that nobody could delete a parent record from the DEPT table if it references at least one EMP record. This ensures that you don't get employees in a nonexistent department, which would be called orphan records. This FK constraint also does not allow you to update any EMP records with new DeptNo value that do not exist in the DEPT table. As you see, this FK constraint protects DEPT and EMP data integrity by always making sure that data are completely in sync.

It is possible to specify an FK declaration that would automatically delete all child records when the parent record gets deleted. This could be achieved with the ON DELETE CASCADE clause:

alter table EMP add constraint EMP_DEPTNO_FK foreign key (DEPTNO) references DEPT(DEPTNO) on delete cascade;

This is dangerous and it could lead to unexpected results, because data in child tables would be removed automatically in the background without informing the user. It provides the cascading capability only for the DELETE operation; for UPDATE you have to write a database trigger to achieve that.

Usually, FK constraints are supported by corresponding indexes. (In our example, it is an index on the DeptNo column in the EMP table.) However, the FK constraint declaration does not automatically create a supporting index. Unindexed FK constraints can often lead to poor performance.



Foreign key (FK) constraints without supporting indexes can cause severe performance problems during SQL queries and massive DML (Data Manipulation Language) operations, such as DELETEs or UPDATEs.



Use a SQL script to detect unindexed foreign key constraints, review each constraint case individually, and either add an index—if it doesn't slow down your DML operations substantially—or disable FK constraints just to perform massive DML operations, and then enable them back.



Comment and Contribute

 

 

 

 

 


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

 

 

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