By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,401 Members | 820 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,401 IT Pros & Developers. It's quick & easy.

Converting a correlated subquery to a non-correlated one

P: n/a
Hi!

Does anyone here know of a way to goad DB2 into converting a correlated
subquery to a non-correlated one? Does DB2 ever do such a conversion?

We have a query of the form
SELECT .. FROM A
WHERE EXISTS (SELECT 'X' FROM B, C
WHERE B.COL1 = A.COL1
AND B.COL2 = A.COL2
AND [ predicates that join B and C ]
AND C.COLP = 'LIT1'
AND C.COLQ = 'LIT2'
)

The access path for this involves a tablespace scan of A, and for each
row retrieved, execution of the subquery, which involves accessing B
first, and C next, and doing a nested loop join between them.

For this specific query, the most efficient order of table access
happens to be C first, B next, then A. The predicates on C.COLP and
C.COLQ eliminate most of the rows. Unfortunately, there doesn't appear
to be a way to get DB2 to choose this table order. I collected all the
detailed statistics that visual explain asked for, but DB2 doesn't
change its access path.

The query performs much better (1 second as opposed to 3 mins) when it
is rewritten this way:

SELECT ... FROM A
WHERE (COL1, COL2) IN (SELECT B.COL1, B.COL2
FROM B, C
WHERE [ predicates joining B and C ]
AND C.COLP = 'LIT1' AND C.COLQ = 'LIT2'
)

The subquery is evaulated only once: C is accessed first, and is joined
with B.

Is there a way to get DB2 to transform a correlated subquery to a
non-correlated one by itself?

Thanks and Regards,
Venkata

Nov 12 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Did you look at changing the optimization level? You also didn't state
what level you are presently using.

Phil Sherman

Venkata C wrote:
Hi!

Does anyone here know of a way to goad DB2 into converting a correlated
subquery to a non-correlated one? Does DB2 ever do such a conversion?

We have a query of the form
SELECT .. FROM A
WHERE EXISTS (SELECT 'X' FROM B, C
WHERE B.COL1 = A.COL1
AND B.COL2 = A.COL2
AND [ predicates that join B and C ]
AND C.COLP = 'LIT1'
AND C.COLQ = 'LIT2'
)

The access path for this involves a tablespace scan of A, and for each
row retrieved, execution of the subquery, which involves accessing B
first, and C next, and doing a nested loop join between them.

For this specific query, the most efficient order of table access
happens to be C first, B next, then A. The predicates on C.COLP and
C.COLQ eliminate most of the rows. Unfortunately, there doesn't appear
to be a way to get DB2 to choose this table order. I collected all the
detailed statistics that visual explain asked for, but DB2 doesn't
change its access path.

The query performs much better (1 second as opposed to 3 mins) when it
is rewritten this way:

SELECT ... FROM A
WHERE (COL1, COL2) IN (SELECT B.COL1, B.COL2
FROM B, C
WHERE [ predicates joining B and C ]
AND C.COLP = 'LIT1' AND C.COLQ = 'LIT2'
)

The subquery is evaulated only once: C is accessed first, and is joined
with B.

Is there a way to get DB2 to transform a correlated subquery to a
non-correlated one by itself?

Thanks and Regards,
Venkata


Nov 12 '05 #2

P: n/a
I apologize for not mentioning that we are running this query on DB2
for z/OS. Optimization level doesn't appear to be available as an
option on DB2 for z/OS.

Thanks,
Venkata

Nov 12 '05 #3

P: n/a
If there is an index (COL1, COL2) on table A.
This(correlation in FROM clause) might be another way:
SELECT A1.*
FROM (SELECT B.COL1, B.COL2
FROM B, C
WHERE [ predicates that join B and C ]
AND C.COLP = 'LIT1'
AND C.COLQ = 'LIT2'
) B1
, TABLE
(SELECT ...
FROM A
WHERE B1.COL1 = A.COL1
AND B1.COL2 = A.COL2
) A1

Nov 12 '05 #4

P: n/a
Assuming statistics are accurate, etc, I would suggest opening a performance
PMR.

"Venkata C" <ve*************@gmail.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...
Hi!

Does anyone here know of a way to goad DB2 into converting a correlated
subquery to a non-correlated one? Does DB2 ever do such a conversion?

We have a query of the form
SELECT .. FROM A
WHERE EXISTS (SELECT 'X' FROM B, C
WHERE B.COL1 = A.COL1
AND B.COL2 = A.COL2
AND [ predicates that join B and C ]
AND C.COLP = 'LIT1'
AND C.COLQ = 'LIT2'
)

The access path for this involves a tablespace scan of A, and for each
row retrieved, execution of the subquery, which involves accessing B
first, and C next, and doing a nested loop join between them.

For this specific query, the most efficient order of table access
happens to be C first, B next, then A. The predicates on C.COLP and
C.COLQ eliminate most of the rows. Unfortunately, there doesn't appear
to be a way to get DB2 to choose this table order. I collected all the
detailed statistics that visual explain asked for, but DB2 doesn't
change its access path.

The query performs much better (1 second as opposed to 3 mins) when it
is rewritten this way:

SELECT ... FROM A
WHERE (COL1, COL2) IN (SELECT B.COL1, B.COL2
FROM B, C
WHERE [ predicates joining B and C ]
AND C.COLP = 'LIT1' AND C.COLQ = 'LIT2'
)

The subquery is evaulated only once: C is accessed first, and is joined
with B.

Is there a way to get DB2 to transform a correlated subquery to a
non-correlated one by itself?

Thanks and Regards,
Venkata

Nov 12 '05 #5

P: n/a

Tonkuma wrote:
If there is an index (COL1, COL2) on table A.
This(correlation in FROM clause) might be another way:
SELECT A1.*
FROM (SELECT B.COL1, B.COL2
FROM B, C
WHERE [ predicates that join B and C ]
AND C.COLP = 'LIT1'
AND C.COLQ = 'LIT2'
) B1
, TABLE
(SELECT ...
FROM A
WHERE B1.COL1 = A.COL1
AND B1.COL2 = A.COL2
) A1


That was a beautifully written query, though it needs the A(col1, col2)
index to be a unique one.

Now, if only I can get DB2 to do this transformation by itself!!
Unfortunately, the original query text, coming from PeopleSoft, cannot
be changed.

Thanks,
Venkata

Nov 12 '05 #6

P: n/a

Mark Yudkin wrote:
Assuming statistics are accurate, etc, I would suggest opening a performance
PMR.

"Venkata C" <ve*************@gmail.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...
Hi!

Does anyone here know of a way to goad DB2 into converting a correlated
subquery to a non-correlated one? Does DB2 ever do such a conversion?

We have a query of the form
SELECT .. FROM A
WHERE EXISTS (SELECT 'X' FROM B, C
WHERE B.COL1 = A.COL1
AND B.COL2 = A.COL2
AND [ predicates that join B and C ]
AND C.COLP = 'LIT1'
AND C.COLQ = 'LIT2'
)

The access path for this involves a tablespace scan of A, and for each
row retrieved, execution of the subquery, which involves accessing B
first, and C next, and doing a nested loop join between them.

For this specific query, the most efficient order of table access
happens to be C first, B next, then A. The predicates on C.COLP and
C.COLQ eliminate most of the rows. Unfortunately, there doesn't appear
to be a way to get DB2 to choose this table order. I collected all the
detailed statistics that visual explain asked for, but DB2 doesn't
change its access path.

The query performs much better (1 second as opposed to 3 mins) when it
is rewritten this way:

SELECT ... FROM A
WHERE (COL1, COL2) IN (SELECT B.COL1, B.COL2
FROM B, C
WHERE [ predicates joining B and C ]
AND C.COLP = 'LIT1' AND C.COLQ = 'LIT2'
)

The subquery is evaulated only once: C is accessed first, and is joined
with B.

Is there a way to get DB2 to transform a correlated subquery to a
non-correlated one by itself?

Thanks and Regards,
Venkata


We will probably do that. Thank you.

Venkata

Nov 12 '05 #7

P: n/a
I think that the A(col1, col2) index don't need to be a unique one.
In your original query, if there are multiple rows in table A of which
(COL1, COL2) values are same, all such rows will be selected.
But, if (B.COL1, B.COL2) is not unique, DISTINCT should be specified in
first subquery in my example.

SELECT A1.*
FROM (SELECT DISTINCT B.COL1, B.COL2
FROM B, C
WHERE [ predicates that join B and C ]
AND C.COLP = 'LIT1'
AND C.COLQ = 'LIT2'
) B1
, TABLE
(SELECT ...
FROM A
WHERE B1.COL1 = A.COL1
AND B1.COL2 = A.COL2
) A1

Nov 12 '05 #8

P: n/a
You are correct. My mistake.

I'm going to safe keep your version as an example of forcing join order
on DB2. Thank you.

Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.