from table 1 A, table 2 B, table 3 C
create table b as select
from table a A, table 2 B, table 3 C
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?
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.