Joachim Zobel wrote:
> 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
>
thanks - that did the trick! Although it turns out I may not need the
AND EXISTS sub select,(which really speeds things up) if the new value
in :new.col2 HAS to exist in col_f in table S2.table2, I have to check
with the users about this. However, even if I do, implementing your
suggestions cut it down to 00:00:00.02 from 6 seconds.
Received on Thu Feb 03 2005 - 12:27:24 CST
