How is this possible?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: How is this possible?

  1. #1
    Join Date
    Nov 2002
    Posts
    1

    How is this possible?

    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?

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,459
    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

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    post execution plan of your query

  4. #4
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width