Re: Deleting detail records with SQL

Thanks to all for your comments. This was be a one-shot run as, sadly, we are migrating to SAP (Severe A.. Pain), based on HP/Oracle/Unix. Just that, in this case, I got curious about the time the sentence was taking.

**Charles,
The actual SQL was quite similar to the one posted, as HEADER2 was a subset (made with "Create table") of the full HEADER file. It was a lot less than 10% of the Detail file, so the that rule shouldn't apply.

**Lloyd,
Thanks for the idea. If I can, I'll check your suggestion.

**Elvis,
Valid arguments. This was a one-time deal, but we thought it was taking a long time and, checking the job, it seemed to be reading the whole DETAIL file.


**Raul,
Thanks for the suggestion. A prior test seemed to suggest about the same time as the "where exists" option. If I have the time (hardly ilkely, these days :-( ) I'll do some more tests just for the heck of it...


---------------------------------------------------------------------------
** Charles Wilt wrote:

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.

---------------------------------------------------------------------------
** Loyd Goodbar wrote:

Most of the time, you tell SQL _what_ you want to do, not _how_ to do it. If
the query optimizer has enough index information to make looking in header2
a quick operation, does it matter? Sometimes the optimizer needs help
understanding what you intend to do, but you shouldn't approach SQL from
that angle.

You can rewrite the query slightly like this:

delete from detail where invoice in (select invoice from header2 where
some_conditions)

With the proper indexes on the detail and hieader2 files this should perform
reasonably well.

---------------------------------------------------------------------------


** Elvis Budimlic wrote:

This kind of delete shouldn't take long.
How long does it really take? Is there significant paging taking place?
Have you tried allocating more memory to this process? Perhaps prebring the
objects into main memory using SETOBJACC prior to DELETE?

Elvis

---------------------------------------------------------------------------
***Raul A. Jager W. wrote:

Try:

Delete from DETAIL Where invoice in ( Select invoice from HEADER2 )

This is for deleting all the detail records that have a HEADER2, in the
second select you can put a where if you need to delete only the detail
for the records that meet a condiction in header2.

---------------------------------------------------------------------------
Luis Rodriguez 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