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?