Re: need trigger suggestions (works but is SLOW)

On Wed, 02 Feb 2005 19:07:40 +0000, Glen S wrote:

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

1. select distinct col_value into new_num from is nonsense because with into there can be only one (and there _must_ be one). You can save the possibly expensive DISTINCT by doing select col_value into new_num from ... WHERE ROWNUM=1;
2. > and :new.col2 in (select distinct col_f
   > from S2.table2 D, S2.table3 E
   > where D.col_b = E.col_a);
   

It has already been pointed out that IN does an implicit DISTINCT. It might save another expensive DISTINCT if you rewrite this to

AND EXISTS (
  SELECT 1
  FROM S2.table2 D, S2.table3 E
  WHERE D.col_b = E.col_a
  AND col_f=:new.col2
);

Maybe you want break this up into 2 seperate SELECTs.

Hth,
Joachim

-- 
Warnung: \" kann Augenkrebs verursachen.