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

Peter Backlund

Union on the same table
(in response to Peter Vanroose)
Test-message - just ignore

Peter

On 2017-10-08 09:47, Peter Vanroose wrote:

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/
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
--

+--------------------------------+---------------------------------+
| Peter G Backlund | E-mail: [login to unmask email] |
+--------------------------------+---------------------------------+
| Phone: 0046(0)70 - 764 7554 | Skype: BacklundDB2 |
+--------------------------------+---------------------------------+
| May - September | October - April |
+------------------------------------------------------------------+
| Råsundavägen 94 | Calle Alella 55, Apt 2B |
| S-169 57 SOLNA | 03185 Torrevieja |
| Sweden | Spain |
+--------------------------------+---------------------------------+
| Playing with DB2 since 1981 ... and forever! |
+------------------------------------------------------------------+

Michael Hannan

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



In Reply to Peter Vanroose:

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.

Peter,

You don't say exactly why you want to do the rewrite. Your CTE t, does not contain col3 or col4. I guess that was just an oversight.

I agree more so with Steen. UNION comes with sort overheads to remove the duplicates, although could be a very small sort. DB2 for z/OS is not going to rewrite the query to use OR instead. Therefore the table gets processed twice for each side of the UNION, unless both sides can be "Index Only" on two different indexes, each potentially with two matching columns. We  still have the duplicate removal to be done though. e.g Indexes by col1, col3, colx and by col1, col4, colx. This UNION approach with Index Only could be useful when a large number of very randomly spread rows will qualify. 

If we don't need Index Only access, the better way to perform this is human rewrite on z/OS, to avoid processing data rows twice, and duplicate removal:
SELECT colx FROM tbl1
WHERE col1='X'
AND  (col3 = 1    OR     col4 = 'Y')
;

The Optimizer could choose a single index access path or Multi-Index access path with List Prefetch.

My comments mostly apply to z/OS for my particular knowledge.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd