CTAS blowing my temp space.....

[Horace]

Hi guys,

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

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.......

-------------------------------------------------------------------------------------------------------------------
| 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??

[gandolf989]

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?

[slimdave]

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?

[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......

[Horace]

And no cartesian product, that would be in the plan operation wouldn't it??

[gandolf989]

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......

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?

[gandolf989]

Also I think you have a typo with the NVL.

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;

[Horace]

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.

[jmodic]

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.

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:

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?

[mike9]

could you please copy paste the error message you get.

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