Union on the same table

Ravikumar sri2001

Union on the same table

Experts,

I tried to find answer for the below question by going thru Db2 manuals and various web sites, but I could not find answer. So, I am posting here.

Assume there is no index on TBL1 and it has 100 pages. Please note in the below query both SELECT has same predicate-1. only 2nd predicate differs.

SELECT COLX FROM TBL1
WHERE COL1 = 'X' AND
COL3 = 1
UNION
SELECT COLX FROM TBL1
WHERE COL1 = 'X' AND
COL4 = 'Y'

In this case,

will Db2 issue 100 Getpages to execute 1st SELECT and again issue 100 Getpages to execute 2nd SELECT ?

(or)

will Db2 issue 100 Getpages and execute both SELECTs simultaneously ?

 

Mark Doyle

Union on the same table
(in response to Ravikumar sri2001)
You didn't mention a platform, but on z/OS, I would expect a query rewrite to write out the union resulting in a single select using "Where col1 ='X' and (col3=1 or col4 ='Y')" But I don't have a system to confirm that right now.

Mark Doyle

Sent from AOL Mobile Mail
Get the new AOL app: mail.mobile.aol.com

On Saturday, October 7, 2017 Ravikumar sri2001 <[login to unmask email]> wrote:

Experts,

I tried to find answer for the below question by going thru Db2 manuals and various web sites, but I could not find answer. So, I am posting here.

Assume there is no index on TBL1 and it has 100 pages. Please note in the below query both SELECT has same predicate-1. only 2nd predicate differs.

SELECT COLX FROM TBL1
WHERE COL1 = 'X' AND
COL3 = 1
UNION
SELECT COLX FROM TBL1
WHERE COL1 = 'X' AND
COL4 = 'Y'

In this case,

will Db2 issue 100 Getpages to execute 1st SELECT and again issue 100 Getpages to execute 2nd SELECT ?

(or)

will Db2 issue 100 Getpages and execute both SELECTs simultaneously ?

 


Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]

Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
http://www.ESAIGroup.com/idug


Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Steen Rasmussen

Union on the same table
(in response to Ravikumar sri2001)
If you EXPLAIN the query, you should get a hint how Db2 will execute the statement. Since you are not using UNION ALL, Db2 will have to do a SORT too in order to eliminate duplicates.

Steen Rasmussen

From: Ravikumar sri2001 [mailto:[login to unmask email]
Sent: Saturday, October 07, 2017 11:56 AM
To: [login to unmask email]
Subject: [DB2-L] - Union on the same table

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.

Experts,

I tried to find answer for the below question by going thru Db2 manuals and various web sites, but I could not find answer. So, I am posting here.

Assume there is no index on TBL1 and it has 100 pages. Please note in the below query both SELECT has same predicate-1. only 2nd predicate differs.

SELECT COLX FROM TBL1
WHERE COL1 = 'X' AND
COL3 = 1
UNION
SELECT COLX FROM TBL1
WHERE COL1 = 'X' AND
COL4 = 'Y'

In this case,

will Db2 issue 100 Getpages to execute 1st SELECT and again issue 100 Getpages to execute 2nd SELECT ?

(or)

will Db2 issue 100 Getpages and execute both SELECTs simultaneously ?



-----End Original Message-----

Peter Vanroose

Re: Union on the same table
(in response to Ravikumar sri2001)

As Steen mentioned, you can easily find out what Db2 does by asking Explain:
With DataStudio, type the query in an SQL window and click the "Visual Explain" button.
If you see the table appearing twice at the bottom of the diagram, it's indeed doing 200 getpages.

If this is indeed the case, and you have the option of rewriting the query (but still want to keep the UNION instead of rewriting to a combined AND & OR, as Mark proposed, maybe because you want a UNION ALL to see rows which satisfy both conditions twice), use a common table expression (CTE), or possibly even a VIEW definition:

WITH t AS (SELECT colx FROM tbl1 WHERE col1='X')
SELECT * FROM t WHERE col3 = 1
UNION
SELECT * FROM t WHERE col4 = 'Y'

Again, verify with Visual Explain whether the table is now accessed just once, or still twice.

In Reply to Ravikumar sri2001:

SELECT COLX FROM TBL1

WHERE COL1 = 'X' AND
COL3 = 1
UNION
SELECT COLX FROM TBL1
WHERE COL1 = 'X' AND
COL4 = 'Y'

will Db2 issue 100 Getpages to execute 1st SELECT and again issue 100 Getpages to execute 2nd SELECT ?

(or)
will Db2 issue 100 Getpages and execute both SELECTs simultaneously ?

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/

Ravikumar sri2001

Re: Union on the same table
(in response to Peter Vanroose)

Thank you so much every one