First of all it seems that in this case the subquery is not needed.
Now, regarding using a non-correlated versus correlated subqueries
is a matter of available indexes and quantity of data. Although
it’s a bit dated, this is still a pretty good explanation
Now, regarding row expressions. DB2 is able to match of leading
index columns for row expressions, and if not they are processed as
stage 2 predicates (my information may be a bit dated, but
basically you only want to use them if they are indexable).
Daniel L Luksetich
DanL Database Consulting
IBM GOLD Consultant
IBM Champion for Analytics
IDUG Content Committee Past-Chairman
IBM Certified Database Adminstrator – DB2 11 DBA for z/OS
IBM Certified System Administrator – DB2 11 for z/OS
IBM Certified Application Developer – DB2 11 for z/OS
IBM Certified Advanced Database Administrator – DB2 10.1 for
Linux UNIX and Windows
From: Michael Hannan [mailto:[login to unmask email]
Sent: Thursday, December 14, 2017 8:03 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Question on DELETE with subselect, as
opposed to a direct DELETE
Db2 is probably transforming the IN subquery into a join like
access path, and I agree the Sort is for duplicate removal when
col1, col2, and col3 do not form a unique key.
Through transitive closure, all the local subquery predicates are
implied to apply to the DELETE table as well. So you are quite
correct, that the IN subquery has no use at all. Probably the coder
misunderstood some technique and applied it incorrectly. Would not
be the first time we ever saw an unnecessary join or subquery. On a
slight tangent, in the old days people talked about joining a table
to itself in order to overcome Stage 2 nature of a predicate like
COL1 = COL2. That really made no sense as well.
If a subquery is needed in a Delete (not in this example), I don't
like to see IN construct unless DB2 can definitely internally
rewrite it as a join access path. I prefer a Correlated EXISTS
subquery if a good index is available.
The IN subquery with DELETE could be useful to limit the number of
keys being deleted in a unit of work (before COMMIT) if the
subquery included a FETCH FIRST n ROWS ONLY clause and perhaps a
DISTINCT. In DB2 V12 the FETCH FIRST n ROWS ONLY clause can appear
directly on the DELETE, probably reducing need for DELETEs with
self referencing subqueries.
In Reply to Kal Sub:
I see a delete statement in an application code like this :
DELETE from Table1 where (col1, col2, col3) in (select col1, col2,
col3 from table1 where col1 = ? and col2 = ? and col3 < current
timestamp - 7 days).
Could I not just write this as
DELETE from Table1 where col1 = ? and col2 = ? and col3 <
current timestamp - 7 days
The access path for the first query shows 3 lines - an index access
with matchcols 2 (for the subquery), and then a sort (due to the
IN), and then again an index access with matchcols 2 (for the outer
The access path for the second query is just 1 line - an index
access with matchcols 2.
I don't know why the statement was coded with a subquery
originally. I seem to remember vaguely that the subquery approach
was suggested sometimes to avoid lock contention on the table. But
I am not sure if that is really right in this situation.
Appreciate your suggestions.
DB2 Application Performance Specialist
CPT Global Ltd
-----End Original Message-----