 |
|
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.
|
