I have following two statements:

Statement 1:

create table a as select
....

from table 1 A, table 2 B, table 3 C
where A.something=B.something
A.something=C.something


Statement 2:

create table b as select
....

from table a A, table 2 B, table 3 C
where A.something=B.something
A.something=C.something


The size of table
Table 1 (3.3 million rows)>Table a (2.9 Million rows)>table b(2.4 million rows)


The number of column in table1, table a, table b are about the same.


When I run statement 1, it took about 1 hours to create table a. But when I run statement 2, table b was not created. What could be wrong? During the process of runing statement 2, I observed that temp table space grows rapidly, until it hits it limit, and return the error message that "can not extend temp table space".

I ran select count(*) withe the same joins as statement 2 and it returned 2.4 million rows within 1 minutes. What could be wrong?