Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!news.glorb.com!transit.nntp.hccnet.nl!transit1.nntp.hccnet.nl!reader.nntp.hccnet.nl!not-for-mail From: Sybrand Bakker Newsgroups: comp.databases.oracle.server Subject: Re: need trigger suggestions (works but is SLOW) Date: Wed, 02 Feb 2005 22:40:12 +0100 Reply-To: sybrandb@hccnet.verwijderdit.nl Message-ID: References: <0y9Md.98189$Qb.44072@edtnps89> X-Newsreader: Forte Agent 1.93/32.576 English (American) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Lines: 51 Organization: HCCnet Nieuwsgebruiker NNTP-Posting-Date: 02 Feb 2005 21:40:10 GMT NNTP-Posting-Host: 62.251.33.219 X-Trace: 1107380410 reader10.nntp.hccnet.nl 769 62.251.33.219:4283 X-Complaints-To: abuse@hccnet.nl Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:234845 On Wed, 02 Feb 2005 19:07:40 GMT, 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 >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