need trigger suggestions (works but is SLOW)

From: Glen S <whoknows_at_no_spam.com>
Date: Wed, 02 Feb 2005 19:07:40 GMT
Message-ID: <0y9Md.98189$Qb.44072@edtnps89>

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

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 Received on Wed Feb 02 2005 - 13:07:40 CST