Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> need trigger suggestions (works but is SLOW)

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US