🚀 go-pugleaf

Romain,

Romain Strieff [TeamB] wrote:
> No idea, are there _simple_ indexes for the involved fields?

No, but there are indexes that _begin_ with the involved field.  Adding
a simple index didn't affect performance.

I believe your earlier example could be simplified to omit the condition
within the subquery:

  DELETE FROM MASTER M WHERE NOT M.ORDER_ID IN;
      (SELECT ORDER_ID FROM CHILD C)

But even with that simplification, executing a SELECT with the "NOT IN
(subquery)" approach takes 1 minute 39 seconds on a medium-size table,
compared to 7 seconds for the "OUTER JOIN" approach.  And "delete for
not seek(key,alias)" takes 4 seconds.

I can only guess that the IN operator is not very efficient; maybe it
was tested more on short lists of values.  Too bad, as it does seem to
be the most elegant solution.

So I guess it's OUTER JOIN to get a rowset for displaying the records,
and "delete for not seek()" to do the actual deletion.  So much for
OODML.

Thanks again for your input.  I'm frustrated by the difficulties of
performing this simple operation, but I'm grateful for the SQL lesson!

Regards,

Mark Berry