Help needed in diagnosing a Query!!!

Database

Help needed in diagnosing a Query!!!

Hi,

I have an issue with a query.

Consider the following query.

Select A.X,
A.Y,
A.Z
From Table A
Where
A.ID IN
(Select lower(ID) from Table B)

The above Query when executed exceeded spool space and threw an error.

But the same Query when given with a GROUP BY Clause as follows Worked FINE!!!

Select A.X,
A.Y,
A.Z
From Table A
Where
A.ID IN
(Select lower(ID) from Table B Group by 1)

The reason for my confusion is

--> There is only one column in Table B(ID)

--> There are totally only 120 rows
(No duplicates. Select lower(ID) From Table B also returns 120 rows
without Group by)

So how come introducing Group by resulted in lesser spool space usage?

Please let me know if you have some inputs on this.

Regards,
Annal Tamizhnambi

2 REPLIES

Re: Help needed in diagnosing a Query!!!

Hi,
I'm not sure, but I think that with the 'lower', the column is agregated.
These types of columns need a group by.

If it isn't, somebody correct the post.

Re: Help needed in diagnosing a Query!!!

The group by is an aggregation step and results in redistribution of the spool of the table B based on the columns grouped. So that might have impacted the join plan of the optimizer.

May the optimizer was initially duplicating table A on all the AMPs and with the introduction of group by, it might instead have decided to do that to table B.

What does the explains of both queries say ?

And what statistics do you have collected against your tables ? what's their PI ?