Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Tue, 05 Mar 2013 @ 16:43:00 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: Performance of deleting records with date range in PPI
From:   Dieter Noeth

Edgar Ng wrote:

  The customer is trying to delete various months / weeks / days of transaction data off a transaction table with PPI set up with transaction date, which method suits the best performance if we are deleting a segement of data (e.g. if we are maintaining history for 3 years, so every day / month, we will delete off the data that is older than 3 years)?  

  1. Drop range on PPI  

Ok, if there's no NO RANGE/UNKNOWN partition, otherwise all "dropped" rows are moved to NO RANGE. But when you simply delete the rows (#2) and keep stats up-to-date, you don't have to drop the partitions.

If you're not running out of partitions better define the partitioning until 2020 (or whatever date far in the future), so you don't have to care anymore about adding/dropping partitions.

  2. Delete from where transaction_date < ?specified date range? (will performance increase if PPI are set up with transaction date?)  

If you specifiy full partitions (e.g. no partial month for monthly partitions) this should be the fastest. It's similar to a FastPath DELETE ALL *if* there's no Secondary/Join Index.

  3. Create a temporary table, insert into temporary table with transaction_date >= ?specified date range? , delete the table, rename the temporary table to target table name  

Long years ago before there was partitioning this might have been fast, but now it should be the slowest :-)


  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023