 |
 |
Archives of the TeradataForum
Message Posted: Tue, 05 Mar 2013 @ 16:43:00 GMT
| 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)? | |
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 :-)
Dieter
| |
