Archives of the TeradataForum

Message Posted: Tue, 15 Jun 2004 @ 13:12:30 GMT

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

Rohini

Both bteq (or sql) deletes and mload deletes are appropriate in the correct circumstances.

If you wish to delete all rows in a table, I suggest that you consider the sql option. In this case, Teradata marks the table header with an indicator to say that the table is empty. Assuming there are no locks on the table when this delete is requested, the delete will complete in a second or two.

Mload deletes are good for deleting large volumes of data, but not all data, from a table. In this case, mload reads the data block by block. It will "touch" each block consumed by the table and remove rows as appropriate and replace the block.

This can be very efficient and will beat a SQL delete in may cases as there is no rollback logging. However, this mload must complete or the table will remain in an unstable state. For it to work best, I have found that if you drop any secondary indexes, run the mload delete and then replace the indexes. This is quicker and it ensures that the work table is small and this also reduces the chances of the mload failing.

Space permitting, it can also be an option to create an empty table, insert into this table the rows that you wish to retain and drop the original table and then rename the new table to the name of the original table.

It is really a case of horses for courses.

Cheers

Frank

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