
MainframeSupports tip week 5/2014:
Some DB2 versions ago two new alternatives to UNION were introduced. They are called
INTERSECT and EXCEPT. INTERSECT selects those rows that are common to both of the two SQL
statements INTERSECT is written between. Even more interesting is EXCEPT which only selects
the rows from the first SELECT that are NOT present in the second SELECT. This is exactly
why EXCEPT can be used as a replacement for NOT EXISTS.
I will now demonstrate EXCEPT using an example. The task is to find those tables in the
system catalogue that does not have any indexes defined on them. Normally you will use this
SQL statement:
SELECT t.name
FROM sysibm.systables t
WHERE t.creator = 'SYSIBM'
AND t.type = 'T'
AND NOT EXISTS
( SELECT 0 FROM sysibm.sysindexes i
WHERE i.tbcreator = t.creator
AND i.tbname = t.name
)
By using EXCEPT instead the task can be solved like this:
SELECT t.name
FROM sysibm.systables t
WHERE t.creator = 'SYSIBM'
AND t.type = 'T'
EXCEPT
SELECT i.tbname
FROM sysibm.sysindexes i
WHERE i.tbcreator = 'SYSIBM'
The result of these two SQL statements is exactly the same list of tables in the system
catalogue without indexes defined on them. The difference lies completely in the way DB2
solves the task. In both cases DB2 will start by finding all tables in SYSIBM.SYSTABLES that
fulfulls the conditions in the WHERE clause.
In the first example DB2 will for each matching table examine the presence of a row in
SYSIBM.SYSINDEXES belonging to the table. If no such row is found the table is included in
the final result. In the second example DB2 will find all indexes in SYSIBM.SYSINDEXES
matching the conditions in the WHERE clause. Afterwards DB2 will remove the rows returned
by the first SELECT that are equal to rows in the second SELECT.
Whether EXCEPT or NOT EXISTS is the best solution will depend on the task to be solved.
I will start by coding my solution as a NOT EXISTS, but if the statement executes very
slowly I will try using EXCEPT instead. Generally NOT EXISTS will require less CPU but have
longer elapsed time when dealing with many rows. It is the opposite when using EXCEPT.
Previous tip in english
Forrige danske tip
Tip list
|