Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Overhead of Constraints on DML Performance
Oracle Tips by Burleson
 

Referential integrity constraints are added to table to enforce business rules. The constraints that affect DML can include check constraints, primary key constraints, and foreign key constraints. No matter how good referential integrity is for maintaining the integrity of the application, RI constraints are the bane of DML performance. Specifically, we see the following issues:

·        Check constraints A check constraint is used to verify that valid values are placed inside specific data columns. For large-volume insert or update operations, a significant amount of time is spent checking the valid values for each column (Figure 17-7).

Figure 7: Overhead of a check constraint

  • Primary key constraints The primary key constraint is enforced by a unique B-tree index on the column, and the index overhead on insert and delete operations can slow down large-volume inserts or updates, since each value must be looked up in the index (Figure 17-8).

Figure 8: Primary key overhead for DML

  • Foreign key constraints These constraints enforce intertable data relationships. For example, a foreign key constraint could be defined such that an order row cannot be added unless a matching customer row exists. The checking of intertable foreign key constraints requires several steps. The data dictionary must first retrieve the name of the foreign key column and then access the foreign table to ensure that the required value is present. Only after this verification is the row added to the target table (Figure 17-9).

Figure 9: Foreign key integrity checking for DML

It should be obvious that that RI constraints are only meaningful during an insert or update DML statement, and there are some techniques employed by DBAs to make large-volume DML tasks run faster.

When performing large-volume insert or update tasks, it is possible to temporarily disable all constraints that reference the affected table. After the load has been completed, the RI constraints are re-enabled, and any errors or exceptions are noted.

This is a well-known performance technique for SQL*Loader, Oracle imports, and large batch inserts and updates. It is far faster to disable the constraints, load the data, and re-enable the constraints than it is to perform all of the RI error checking for each row.

It is relatively easy to locate and temporarily disable primary key and check constraints, but it is a bit more tricky to locate foreign key constraints, because they are defined on other tables.

Overhead of Maintaining Indexes with DML

For Oracle systems that are highly indexed, there's an extreme overhead associated with the bulk insertion and updates.

As you may recall from Oracle fundamentals, whenever a row is inserted or modified, all indexes in which that index participates have to be updated in real time. This can often increase the overhead of maintaining batch inserts and updates by an order of magnitude, dramatically slowing down the performance of the system.

In the real world, it is not uncommon for a very large batch DML operation to drop all of the indexes prior to altering the base table. Following the batch update or insert operation, the indexes are rebuilt very quickly through a full-table scan within the Oracle tables. It has been conclusively demonstrated that this kind of approach is often far faster for bulk loading, provided, of course, that all SQL selects are temporarily suspended during the load (Figure 17-10).

Figure 10: Dropping indexes during a bulk DML operation

An additional problem with updating B-tree indexes in place is that the indexes commonly get out of balance. For example, when a large number of rows are added to a table, Oracle will often have to split and spawn that area of the B-tree index, creating an area within the index that may spawn to four and even five levels deep. We also can see a disproportionate number of unbalanced leaf nodes within the index. By dropping the indexes, loading the data, and rebuilding the indexes, the overall time is reduced and you can be assured of well-balanced B-tree indexes.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter