How is this possible?

[rshi]

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?

[LKBrwn_DBA]

You are running ount of temp tablespace, try this:

create table b unrecoverable
as select
....
from table a A, table 2 B, table 3 C
where A.something=B.something
and A.something=C.something

[pando]

post execution plan of your query

[tycho]

Hi Rshi,

Normaly your "create table as select" statement creates a temporary segment in the default tablespace of the user. If you are running out off space in the temporary tablespace you are doing somekind of sort which fills a temporary segment in the temporary tablespace which doesn't fit in.

Can you post both complete statements?

Tycho