Glen S wrote:
> I created a trigger on an app that will update a column on a newly
> inserted row if another column's value(in the new row) matches some
> values in other tables.
>
> basically it is as follows:
> ========================================================================
> declare
> new_num number;
> BEGIN
> select distinct col_value into new_num
You obviosy expect only one group to exist, so why not just take the
first row (ROWNUM < 2)
> from
> S1.table1 A,
> S1.table2 B,
> S2.table1 C,
> S2.table2 D,
> S2.table3 E
> where E.col_a = D.col_b
> and E.col_b = C.col_c
> and C.col_d = A.col_a
> AND A.colb = B.col_d
> AND B.col_e = 'some_text'
> and :new.col2 in (select distinct col_f
IN (SELECT DISTINCT ...)... Can't comment on the Oracle optimizer, but
it should be the optimizers job to decide whether a distinct is
beneficial for IN processing or not: Leave teh DISTINCT away
> from S2.table2 D, S2.table3 E
> where D.col_b = E.col_a);
>
> if :new.col2 is not null
> then :new.col1 := new_num;
Does Oracle support a trigger conditions? Move the condition there:
CREATE TRIGGER .... WHEN (:new.col2 is not null) ...
If not, pull the condition up and don't run the query if :new.col2 is
null. Of course whether you win a lot or not depends on how often col2
is null....
>
> end if;
> end;
> ========================================================================
>
> This is done before insert, and it works but takes about 6 minutes to
> insert the row, which is too slow.
>
> The tables referenced are quite large, and I know there must be a better
> way to do this, if someone can suggest an alternative way that could
> save me a lot of reading I'd be very appreciative!
>
> tia
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Received on Wed Feb 02 2005 - 15:13:01 CST
