Converting a correlated subquery to a non-correlated one

[Venkata C]

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

[Phil Sherman]

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

[Venkata C]

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

[Tonkuma]

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

[Mark Yudkin]

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

[Venkata C]

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

[Venkata C]

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

[Tonkuma]

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

[Venkata C]

You are correct. My mistake.

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