Hi,

At October 26, 2004, 15:07, Ann W. Harrison wrote:

> At 01:03 PM 10/26/2004, sserdyuk_05446 wrote:



>>You are right, it is better this way, but it doesn't change results-
>>still one record did not get deleted.
>>Any other ideas?
>>
>> > Neither. The search field should be qualified:
>> >
>> > delete from SY_REPORT_WRITER_REPORTS t1
>> > where t1.NBR in
>> > (select t2.NBR from SY_REPORT_WRITER_REPORTS t2
>> > where t2.ACTIVE_RECORD='N')
>> >

> Sigh. The "in" expression is being computed for each record
> rather than once for the whole delete. Once the two records
> with "ACTIVE RECORD = N" are deleted, nothing else matches.
> There are a variety of work arounds.

So, ideally what should be implemented is that self-contained
subqueries like the one above would be executed separately and the
engine would only have to reference the result set, instead of
executing the subquery for each row of the DML. This "could" also
improve performance.

--
Best regards,
Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)