QAD BI v3 - Tech Level 2 Certification - Part 4 > Working With Large Tables > DELETE vs. COPY/TRUNCATE/INSERT
  PPT
DELETE vs. COPY/TRUNCATE/INSERT
DELETE vs. COPY/TRUNCATE/INSERT
DELETE vs. COPY/TRUNCATE/INSERT
DELETE vs. COPY/TRUNCATE/INSERT
 
DELETE from tables take up lots of log space, because it needs to roll back in the event of failure.
DELETE vs. COPY/TRUNCATE/INSERT
DELETE vs. COPY/TRUNCATE/INSERT
DELETE vs. COPY/TRUNCATE/INSERT
 
Create a backup of the original to ensure that if something goes wrong, the integrity of the data from the table to be TRUNCATED is maintained.
DELETE vs. COPY/TRUNCATE/INSERT
DELETE vs. COPY/TRUNCATE/INSERT
 
The inline view within this procedure looks for any instances where the number of records exceeds 150 which is a thumbnail calculation based on 3 consignment types * 12 months * 4 years. It also totals up the absolute value of the quantities begin, change and end for each month. If in that 4+ year span, there have been no changes in quantity to the item_number/site_code combination, the item/site combination was deemed inactive and put in this table to be used as a filter so that going forward only records not in the filter would make it to the fact table.
 
select source_system_code, domain_code, item_number, site_code
into permx_inv_retired_item_sites
from (select source_system_code,
domain_code,
item_number,
site_code,
sum(abs(quantity_changed)+abs(quantity_begin)+abs(quantity_end)) total_chg,
count(*)
from fact_inv_mth_balances_bkup_20140101
group by source_system_code,
domain_code,
item_number,
site_code,
having count(*) > 150 -- 3 consignment types * 12 months * approx 4 years
) x
where total_chg = 0
DELETE vs. COPY/TRUNCATE/INSERT