You Asked

Tom,

Thanks for the excellent site...can't say this enough.

My problem pertains to updating flags (0/1 columns and other calculated fields) on a rather large fact table with millions of records spanning 23 partitions. I use triggers on this table to maintain flags of various types that are used for index lookups. Normal edits (somewhat rare in this data warehouse type database) fire triggers that keep the flags tidy. I have been handling large or massive imports by disabling triggers and a subsequent massive (or limited) recalculation of flags on all (or only the imported) records. Obviously, disabling triggers leaves the data vulnerable during this interval and the time to rebuild all these flags can take 30 minutes (or more). Changing the operations updating the flags has been effective in greatly reducing the time-to-recalculate but the vulnerability is still there. I am not sure how to proceed... It occurs to me that I might be able to perform my multiple partition update by parallellizing each partition in a separate job...but this will not work because each job might enable/disable the triggers...conflicting with another job/partition. Could I take a partition offline and update it while other users cannot? How would I do this? Alternately, perhaps I should not use triggers at all (making much more work for myself) and write procedural code (non-trigger) so that disabling triggers is no longer an issue. Actually those triggers do so much...posting to other tables, etc...I don't think I could manage without them.

I would appreciate your thoughts on this.

and Tom said...

You could code your triggers to be selectively disabled - either via a package global variable - or by adding another column to the table (YAF - yet another flag) - or by using the direct path loader (which won't fire triggers...)

for example:

ops$tkyte@ORA10GR2> create table t ( x int primary key, data varchar2(10) );

Table created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create or replace package trigger_pkg
2 as
3 g_fire boolean default TRUE;
4 end;
5 /

Package created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create or replace trigger t_trigger
2 before insert on t for each row
3 begin
4 if trigger_pkg.g_fire
5 then
6 :new.data := 'trigger';
7 dbms_output.put_line( 'code goes here...' );
8 end if;
9 end;
10 /

Trigger created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> insert into t (x,data) values ( 1, null );
code goes here...

1 row created.

ops$tkyte@ORA10GR2> exec trigger_pkg.g_fire := false;

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> insert into t (x,data) values ( 2, null );

1 row created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select * from t;

X DATA
---------- ----------
1 trigger
2

the 'flag' approach

ops$tkyte@ORA10GR2> alter table t add trigger_flag int;

Table altered.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create or replace trigger t_trigger
2 before insert on t for each row
3 when (new.trigger_flag is null)
4 begin
5 :new.data := 'trigger';
6 dbms_output.put_line( 'code goes here...' );
7 end;
8 /

Trigger created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> insert into t (x,data) values ( 3, null );
code goes here...

1 row created.

ops$tkyte@ORA10GR2> insert into t (x,data,trigger_flag) values ( 4, null, 1 );

1 row created.

ops$tkyte@ORA10GR2> select * from t;

X DATA TRIGGER_FLAG
---------- ---------- ------------
1 trigger
2
3 trigger
4 1

ops$tkyte@ORA10GR2> commit;

Commit complete.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> note: "always" fires - or does it...
ops$tkyte@ORA10GR2> create or replace trigger t_trigger
2 before insert on t for each row
3 begin
4 :new.data := 'trigger';
5 dbms_output.put_line( 'code goes here...' );
6 end;
7 /

Trigger created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> !cat t.ctl
LOAD DATA
INFILE *
INTO TABLE t
append
FIELDS TERMINATED BY '|'
(
x
,data
)
BEGINDATA
5||
6||
7||

ops$tkyte@ORA10GR2> !sqlldr / t direct=y

SQL*Loader: Release 10.2.0.1.0 - Production on Sat Nov 26 14:04:40 2005

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Load completed - logical record count 3.

ops$tkyte@ORA10GR2> select * from t;

X DATA TRIGGER_FLAG
---------- ---------- ------------
1 trigger
2
3 trigger
4 1
5
6
7

7 rows selected.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Excellent

Phil Adams, November 26, 2005 - 2:43 pm UTC

Tom,

The package/global variable...I think that would actually function as a session-specific setting (other sessions would not see it), correct? If that is the case this is a winning soultion.

Your answer reflects your understanding that there are two different operations of interest: updates and inserts. Thanks for pointing out the direct path load functionality...I did forget that feature. With this application/table I pre-load data into an intermediate table and then import using the APPEND hint. I am not sure if this approach will bypass the trigger (like a direct path load)... Can you respond to this? Relatedly, even though I use the APPEND hint my (somewhat vague) understanding is that this hint only "works" when multiple, specific conditions are met. That is, using the hint does not guarantee it will be used.

November 26, 2005 - 4:42 pm UTC

APPEND will not - insert /*+ append */ is "ignored" if the table has triggers/RI on it:

ops$tkyte@ORA10GR2> create table t ( x int );

Table created.

ops$tkyte@ORA10GR2> insert /*+ append */ into t select 1 from dual;

1 row created.

ops$tkyte@ORA10GR2> select * from t;
select * from t
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


ops$tkyte@ORA10GR2> create trigger t_trigger
  2  before insert on t for each row
  3  begin
  4          dbms_output.put_line( 'trigger' );
  5  end;
  6  /

Trigger created.

ops$tkyte@ORA10GR2> insert /*+ append */ into t select 1 from dual;
trigger

1 row created.

ops$tkyte@ORA10GR2> select * from t;

         X
----------
         1
         1

ops$tkyte@ORA10GR2>


<b>see we did not get the error as before - because of the trigger...</b>

Now I am baffled

Phil Adams, November 26, 2005 - 5:42 pm UTC

Your first insert /*+ append */ returned "1 row created" and the subsequent select returned an ORA-12838. Why?...the table did not have any trigger.

After you create the trigger you have another successful insert /*+ append */. Isn't that what your example shows?

I just cannot figure out the ORA-12838.

November 27, 2005 - 9:55 am UTC

in order to see that the append happened (a direct path operation), I queried the table.

IF you get the 12838 after an insert /*+ APPEND */, then APPEND was in place.

IF you do not get the 12838 - it was a conventional path insert.

I demonstrated that without a trigger - insert /*+ APPEND */ was "obeyed", and then WITH a trigger - the APPEND hint was ignored.

Unbaffeled

Phil Adams, November 26, 2005 - 11:23 pm UTC

I think I figured this out by looking at some of your previously answered questions...

Specifically, when the SELECT cannot read from the table following an insert /*+ append */ (i.e produces ORA-12838) this indicates that the insert was DIRECT PATH.

Alternately, when a SELECT can read from the table following an insert /*+ append */ this indicates that the insert was NOT a DIRECT PATH.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Excellent

Now I am baffled

Unbaffeled

More to Explore

PL/SQL demos

PL/SQL docs

Disable triggers for single partition

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Excellent

Now I am baffled

Unbaffeled

More to Explore

PL/SQL demos

PL/SQL docs