How does this joining condition used inside the subquery solve spool issue?

Database

How does this joining condition used inside the subquery solve spool issue?

Hi,

This query seems to be simple and may be discussed already, but pls find time to help such newbie like me.

Select A, B, C, D
from Table1 a,
where a.A in
(
Select b.A
from Table1 b
where b.C = date and
a.A = b.A
)
and a.B = 'something'
and a.D = 'something'
;

FYI - there are some million rows in Table1.

If i use the condition a.A = b.A, outside the subquery it spools out or if i completely remove this condition also, the query spools out.

My guess is that, the output is not going to change even if i use / dont use this condition a.A = b.A.
Is that right?

And how it solves the spool space issue if used inside the subquery instead using it outside the subquery?

Thanks in advance...

Regards,
Balaji
3 REPLIES
Enthusiast

Re: How does this joining condition used inside the subquery solve spool issue?

Are any Indexes defined on Table1 and what type UPI/NUPI, USI/NUSI ?

Why you want to use Sub-query ? I think, the other way, we can write SQL like this, pls try and see if it works

SELECT
A, B, C, D
FROM Table1 a,
(SELECT
b.A
FROM Table1 b
WHERE b.C = date
GROUP BY 1) b
WHERE a.A = b.A
AND a.B = 'something'
AND a.D = 'something'
;

Re: How does this joining condition used inside the subquery solve spool issue?

UPI is for the column A.
The solution that you mentioned was the original query, since it's getting spooled out, i reframed it like this.

Re: How does this joining condition used inside the subquery solve spool issue?

Any help!!!