Re: Deleting detail records with SQL

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Luis,

You'd need to use Visual Explain to see what and why the query engine is
doing what it is.

But, as an FYI it's not unusual or even bad that the engine is doing a full
table scan.  If I recall correctly, once you get above 10% of the records
being affected, it's usually be quicker for the QE to simply scan the whole
file.

You have to poast the actual SQl your running for me to take any other
guesses.  I'm assuming tere's more than what you posted as what you posted
would delete all details for which there was a header.  Assuming your DB is
supposed to have a header for each detail, and in fact does, the just delete
from DETAIL would accomplish the same thing much faster.

Charles


On Wed, Jun 17, 2009 at 12:56 PM, Luis Rodriguez
<luis.rodriguez2@xxxxxxxxx>wrote:

> Hi list,
>
> A coworker gave me the following SQL problem:
>
> I need to erase all the detail from certain records from a header based
> file. Something like:
>
> Delete from DETAIL
> Where exists( Select 1 from HEADER2 where DETAIL.invoice = HEADER2.invoice)
>
> DETAIL has a lot more records than HEADER2. HEADER2 and DETAIL have indexes
> by invoice.
>
> So far, so good. Now, it seems that the system does read (explore) the
> entire DETAIL file in the process (a quite lengthy process).
>
> If I were to do this with RPG, I would reverse the file order. I would read
> HEADER2, position DETAIL by key and, with an inside loop, delete all the
> records where the invoice numbers coincide. Is there any way to emulate this
> behavior with SQL?
>
> Thanks,
>
>
> Luis Rodriguez
>
> IBM Certified Systems Expert
> eServer i5 iSeries Technical Solutions
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.