Archives of the TeradataForum

Message Posted: Thu, 09 Sep 2004 @ 14:33:59 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I am trying to find a way to speed up this SQL. Currently this runs for an hour. There are 130081 rows in the TMP_dpc_status_daily table and 224457 in the TMP_dpc_status_daily2 table.

I have the table ddl below (both tables are identical).

Before I run the update, I have collected statistics on every column and the index.

Any feedback would be greatly appreciated.

Thanks

Craig

     UPDATE a
     FROM dssbatch.TMP_dpc_status_daily a, dssbatch.TMP_dpc_status_daily2
      b
     SET last_sale_date = b.last_sale_date
     WHERE a.branch_id = b.branch_id
     AND a.customer_no = b.customer_no
     AND b.last_sale_date > a.last_sale_date
     OR a.last_sale_date is null;


     CREATE MULTISET TABLE dssbatch.TMP_dpc_status_daily_daily2 ,NO FALLBACK
     ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL
     (
           branch_id SMALLINT,
           customer_no CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
           first_sale_date DATE FORMAT 'YYYY/MM/DD',
           last_sale_date DATE FORMAT 'YYYY/MM/DD'
     )
     PRIMARY INDEX ( branch_id ,customer_no );

	<Prev	Next>		<<First	<Prev	Next>	Last>>