Archives of the TeradataForum

Message Posted: Thu, 17 Sep 2009 @ 23:28:31 GMT

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

On doing deletes be mindful of the transient journaling effect when issuing DELETE in SQL.

If you are not deleting based on Indexes (a nusi) or on partitioned values, even with a small percentage of data being deleted from the tables, it can incur some serious performance costs associated with journaling the deleted rows, depending on how the rows are organized in the data blocks.

An index will be a little better than a regular delete, in that you (probably) won't incur a full table scan.

Deleting based on a PPI, (i.e. removing a months worth of data off a table Partitioned by month) will be very fast b/c of the way the data is ordered on the amps, and in the actual data blocks being affected.

As mentioned earlier in this thread, creating a secondary table is a safe solution, and performance will be fairly consistent.

The performance benefits will depend on the size of the data being inserted from the core table, and the performance of the join or exclusion statement to filter out (implicit delete) the records intended for deletion. This also has the advantage of no blocking/locking on the production table while the ETL is happening. The only downtime on the table will be in the split second when you do the renames. If high availability is a requirement, this may be the best solution.

If you are Always only deleting a small amount of data on indexed fields or the partitioning expression for PPI tables, delete may be way to go. But if you have the possibility of getting a delete which is 50% of a large table it may not be the best standard solution to have.

Thanks,

Eric Barner

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