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