CTAS blowing my temp space.....
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: CTAS blowing my temp space.....

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    CTAS blowing my temp space.....

    Hi guys,

    One of our developers is creating a table.........

    Code:
    CREATE TABLE TAB1 AS 
    select t.*, CASE WHEN p.plan_date is NULL THEN null ELSE p.plan_date END plan_date
    from   w_card_transaction t, tmp_df_plans p
    where  t.account_org = p.account_org(+)
    and    t.account_number = p.account_number(+)
    and    t.statement_id_code = p.statement_id_code(+)
    and    t.statement_sequence_number = p.statement_sequence_number(+);
    The plan says it will use 434 M of tempspace.......

    Code:
    -------------------------------------------------------------------------------------------------------------------
    | Id  | Operation              |  Name               | Rows  | Bytes |TempSpc| Cost  |  TQ    |IN-OUT| PQ Distrib |
    -------------------------------------------------------------------------------------------------------------------
    |   0 | CREATE TABLE STATEMENT |                     |    75M|    10G|       | 12234 |        |      |            |
    |   1 |  LOAD AS SELECT        |                     |       |       |       |       |        |      |            |
    |*  2 |   HASH JOIN OUTER      |                     |    75M|    10G|   434M| 12234 | 91,02  | P->S | QC (RAND)  |
    |   3 |    TABLE ACCESS FULL   | W_CARD_TRANSACTION  |    75M|  6090M|       |  4481 | 91,00  | P->P | HASH       |
    |   4 |    TABLE ACCESS FULL   | TMP_DF_PLANS        |    32M|  1873M|       |   372 | 91,01  | P->P | HASH       |
    -------------------------------------------------------------------------------------------------------------------
    But, it blows my 10G tempspace, and fails.......

    Any ideas why??

    Or do I need a code rewrite??

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    Is there any reason why you aren't using the NVL clause???

    NVL(p.plan_date, p.plan_date) plan_date

    Are you sure that you aren't generating a cartesian product?
    this space intentionally left blank

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Are you sure that it is the segment in the temp tablespace that is raising the error, not the temporary segment created in the permanent tablespace that houses the result set and which will get turned into TAB1 on completion of the CTAS?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Definately the temp tablespace Dave. Upped it from 10 G to 20 G and it still fills up then crashes out. Tried an 'order by' clause in the CTAS in an attempt to stop it sorting in temp, but to no avail.

    Gandolf, I might be being a bit thick here, but how would I incorporate the nvl clause into the code? And how would that help this situation?

    Thanks......

  5. #5
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    And no cartesian product, that would be in the plan operation wouldn't it??

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    Quote Originally Posted by Horace
    Definately the temp tablespace Dave. Upped it from 10 G to 20 G and it still fills up then crashes out. Tried an 'order by' clause in the CTAS in an attempt to stop it sorting in temp, but to no avail.

    Gandolf, I might be being a bit thick here, but how would I incorporate the nvl clause into the code? And how would that help this situation?

    Thanks......
    Code:
    CREATE TABLE TAB1 AS 
       SELECT t.*, NVL(p.plan_date, p.plan_date) plan_date
         FROM w_card_transaction t
         LEFT OUTER JOIN tmp_df_plans p
           ON t.account_org               = p.account_org
          AND t.account_number            = p.account_number
          AND t.statement_id_code         = p.statement_id_code
          AND t.statement_sequence_number = p.statement_sequence_number;
    That the explain plan does not show cartesian product is no guarantee that you are not getting one. What are the primary keys of the two tables? Are you joining on more or less columns than you need? Would an index make the sort more efficient?
    this space intentionally left blank

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    Also I think you have a typo with the NVL.

    Code:
    CREATE TABLE TAB1 AS 
       SELECT t.*, NVL(p.plan_date, t.plan_date) plan_date
         FROM w_card_transaction t
         LEFT OUTER JOIN tmp_df_plans p
           ON t.account_org               = p.account_org
          AND t.account_number            = p.account_number
          AND t.statement_id_code         = p.statement_id_code
          AND t.statement_sequence_number = p.statement_sequence_number;
    this space intentionally left blank

  8. #8
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Hi Gandolf,

    It is a compound primary key on both tables, i.e.,

    account_org,account_number,statement_id_code,statement_sequence_number

    I want to populate TAB1 with plan_date from p.
    If the outer join returns NULL, then NULL is inserted in the new column. If NOT NULL, i.e., a row exists in p. that is not null, then populate the new column with the not null value from p.

    I eventually upper the tempspace to 25G and the table created successfully. I might try to add an index, to test your theory, but surely the optimiser would use the primary key index if it chose to do so.

    Many thanks.

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote Originally Posted by Horace
    I want to populate TAB1 with plan_date from p.
    If the outer join returns NULL, then NULL is inserted in the new column. If NOT NULL, i.e., a row exists in p. that is not null, then populate the new column with the not null value from p.
    Quote Originally Posted by Horace
    Code:
    CREATE TABLE TAB1 AS 
    select t.*, CASE WHEN p.plan_date is NULL THEN null ELSE p.plan_date END plan_date
    from   w_card_transaction t, tmp_df_plans p
    where  t.account_org = p.account_org(+) ....
    So, what's wrong with plain and simple:
    Code:
    CREATE TABLE TAB1 AS 
    select t.*, p.plan_date
    from   w_card_transaction t, tmp_df_plans p
    where  t.account_org = p.account_org(+) ....
    Where is the difference?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Mar 2002
    Posts
    534
    could you please copy paste the error message you get.

    Also how many rows (count(*)) have your 2 tables ?

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