Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.icl.net!newsfeed.fjserv.net!news.tele.dk!news.tele.dk!small.news.tele.dk!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail
From: Serge Rielau <srielau@ca.ibm.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: need trigger suggestions (works but is SLOW)
Date: Wed, 02 Feb 2005 16:13:01 -0500
Lines: 58
Message-ID: <36cu1rF4t8pnnU1@individual.net>
References: <0y9Md.98189$Qb.44072@edtnps89>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Trace: individual.net UoOq63oMk8AEuWrAGloRpwnr1Tip/hVbKFLGfi3M8pEwu7junM
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
In-Reply-To: <0y9Md.98189$Qb.44072@edtnps89>
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:234839

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