Archives of the TeradataForum

Message Posted: Tue, 19 Oct 2010 @ 13:10:15 GMT

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

Hi All,

The DELETE statement has one function and that is to remove rows from a table. A status is the only returned value from the database; no rows are returned to the user.

Delete All:Removes all the rows at a time.

One of the fastest things that Teradata does is to remove ALL rows from a table.

The reason for its speed is that it simply moves all of the sectors allocated to the table onto the free sector list in the AMP's Cylinder Index. It is the fast path and there is no OOPS command, unless the explicit transaction has not yet completed. In that case, a ROLLBACK statement can be issued to undo the delete operation before a COMMIT. Otherwise, the rows are gone and it will take either a backup tape or a BEFORE image in the Permanent Journal to perform a manual rollback. Be Very CAREFUL with DELETE.

Delete: Deletes the particular rows given with a condition .

For instance, the table is supposed to contain twelve months worth of data and it is now month number thirteen. It is now time to get rid of rows that are older than twelve months.

As soon as a WHERE clause is used in a DELETE, it must take the slow path to delete the rows. This simply means that it must log or journal a copy of each deleted row. This is to allow for the potential that the command might fail. If that should happen, Teradata can automatically put the deleted rows back into the table using a ROLLBACK. As slow as this additional processing makes the command, it is necessary to insure data integrity. To use the Fast Path, a technique is needed that eliminates the journal logging.

Regards,

Susmitha.

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