Re: Deleting detail records with SQL

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.