Archives of the TeradataForum
Message Posted: Mon, 24 Sep 2001 @ 19:20:25 GMT
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|