When a UNION is required to put together data from multiple queries, you might be able to use a CASE statement instead. This is very useful, particularly when the data for each of the queries in the UNION come from the same table. The CASE statement can potentially enhance performance by minimizing the number of times the data is read.
Let’s look at an example to clarify why:
SELECT CREATOR, NAME, ‘TABLE’
WHERE TYPE = ‘T’
SELECT CREATOR, NAME, ‘VIEW ‘
WHERE TYPE = ‘V’
SELECT CREATOR, NAME, ‘ALIAS’
WHERE TYPE = ‘A’
ORDER BY NAME;
This simple SQL statement uses UNION to put together the results of three queries against the SYSTABLES table. The report shows all of the DB2 table-like objects that exist in the DB2 subsystem: tables, views, and synonyms.
To do this, DB2 must scan through the table three times – once for each query (as there is no index on the TYPE column). But, you can use CASE and code an equivalent, but more efficient query, as follows:
SELECT CREATOR, NAME,
WHEN ‘T’ THEN ‘TABLE’
WHEN ‘V’ THEN ‘VIEW ‘
WHEN ‘A’ THEN ‘ALIAS’
ORDER BY NAME;
This new query will need to scan SYSTABLES only once. The CASE statement will translate the code in the TYPE column into the text that we desire.
CASE statements are very powerful and you should use them when you can to create elegant and optimal SQL in your DB2 applications.
This post was originally published on Craig Mullins’ blog at: http://db2portal.blogspot.com/2009/11/replacing-union-with-case.html
For additional resources please download my white paper: “The Many Different Types of DBAs”
Subscribe to Our Blog
Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.
The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.
EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.