Archives of the TeradataForum

Message Posted: Mon, 24 Sep 2001 @ 19:20:25 GMT

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

Have you checked the data distribution of 'eti.sales_org_hier' and 'offline_staging_incr_dev.sale_offline_conv'? Do they have a reasonably even distribution? When you run this update, how does the spool space look? Does it have a reasonably even distribution?

How often are you going to do this update? Is it a one-off or something going into production? You might consider changing it into an INS/SEL:

   insert into eti.sales_org_heir_new
      sel soc.sales_org_heir
        , soc.bus_unit_name
        , < any other columns >

        from eti.sales_org_hier                            soh
           , offline_staging_incr_dev.sale_offline_conv    soc

        where soh.terr_id = soc.terr_id
          and soh.online_offline_cd = 'off'
          and oc.on_off = 'offline';

where 'eti.sales_org_heir_new' has the same PI as 'eti.sales_org_heir'. Make sure the _NEW table is empty and then run the INS/SEL. When the INS/SEL has completed, then rename the two 'eti' tables.

The advantages of this approach is that you're taking advantage of the fast-path (you're not redistributing data and you're inserting into an empty table). There's no overhead associated with the change journal. If you need to cancel the INS/SEL, there's no rollback. I also like the idea that you can troubleshoot the query as a SELECT before actually having to deal with the destination table.

Even if you want to do it with an UPDATE, you might try the SELECT so that you can make sure that your data (and therefore the query) is behaving the way that you think.

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