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 -> Re: need trigger suggestions (works but is SLOW)

Re: need trigger suggestions (works but is SLOW)

From: Sybrand Bakker <sybrandb_at_hccnet.nl>
Date: Wed, 02 Feb 2005 22:40:12 +0100
Message-ID: <i3i2015en67pc79r40u20seo33045nsbvs@4ax.com>


On Wed, 02 Feb 2005 19:07:40 GMT, Glen S <whoknows_at_no_spam.com> 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
>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

The distinct in the in subquery is redundant. The result of an in subquery is a set, and a set consists of unique elements. The optimizer will do the distinct on your behalf, but it won't eliminate your distinct.

--
Sybrand Bakker, Senior Oracle DBA
Received on Wed Feb 02 2005 - 15:40:12 CST

Original text of this message

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