that the constraint isn’t currently being violated). It’s a good idea to do this check before
releasing control to some other part of the
program (which may not be expecting the
deferred constraints) or committing:
SQL> set constraint
child_fk_parent immediate;
set constraint child_fk_parent immediate
*
ERROR at line 1:
ORA-02291: integrity constraint
( OPS$TKYTE.CHILD_FK_PARENT) violated –
parent key not found
Code Listing 2: Step 1 to improving slow-by-slow processing—bulking up
declare
cursor c is
select rowid rid, t.*
l_limit number := 1000;
The SET CONSTRAINT fails and returns
an error immediately, as expected, because
the constraint was violated. The UPDATE to
PARENT was not rolled back (that would have
violated the statement-level atomicity); it is
still outstanding. Also note that the transaction is still working with the CHILD_FK_
PARENT constraint deferred because the SET
CONSTRAINT statement failed.
Let’s continue now by cascading the
UPDATE to CHILD:
begin
loop
-- l_arrayN is a plsql index by table corresponding
-- to your columnN - declare of the type of columnN
fetch c bulk collect
into L_ROWID_ARRAY,
LIMIT l_limit;
l_array1, l_array2,
l_array3 ...
SQL> update child set fk = 2;
1 row updated.
SQL> set constraint
child_fk_parent immediate;
Constraint set.
for i in 1 .. l_rowid_array.count
loop
-- the current body of your loop goes here, but
-- instead of an UPDATE, you would assign the
-- values to more arrays (the set columns of your
-- current update), eg:
vrep_array(i) := vrep;
vpaid_array(i) := vpaid;
... and so on ...
forall i in 1 .. l_rowid_array.count
update big_table
set reported = v_rep_array(i),
paid = v_paid_array,
...
where rowid = l_rowid_array(i);
exit when c%notfound;
end loop;
Code Listing 3: The slow_by_slow stored procedure
create or replace procedure slow_by_slow
as
begin
for x in (select rowid rid, object_name
from t t_slow_by_slow)
loop
x.object_name := substr(x.object_name, 2)
||substr(x.object_name, 1, 1);
update t
set object_name = x.object_name
where rowid = x.rid;
end loop;
end;
SQL> commit;
Commit complete.
And that’s the way it works. Note that
to defer a constraint, you must create
it that way—you have to drop and re-create the constraint to change it from
NONDEFERRABLE to DEFERRABLE.
That might lead you to believe that you
should create all of your constraints as
DEFERRABLE INITIALLY IMMEDIATE, just in
case you want to defer them at some point.
In general, however, that is not true. You
want to allow constraints to be deferred only
if you have a real need to do so. By creating
deferred constraints, you introduce differences in the physical implementation
(the structure of your data) that might not
be obvious. For example, if you create a
deferrable UNIQUE or PRIMARY KEY constraint, the index Oracle Database creates to
support the enforcement of that constraint
will be a nonunique index. Normally you
expect a unique index to enforce a unique
constraint, but because you have specified that the constraint can temporarily be
ignored, the database can’t use that unique
index. Other subtle changes will also be
apparent, for example, with NOT NULL
