Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 12 Jun 2002 @ 15:00:28 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: Delete vs delete all
From:   David Wellman


I think you'll find that there is NO difference (performance or functionality) whether or not you use the ALL keyword. Other people have rightly pointed out that when the optimiser recognises that it's being asked to delete all rows from a table then providing this statement is at the end of a transaction (and the optimiser knows that) there will only be a single TJ entry written and Teradata 'cheats'.

What controls the difference between writing individual TJ entries and Teradata cheating is the presence or absence of a WHERE clause. So...

Delete from employee: <== no WHERE clause so Teradata cheats.

Delete from employee where employee_id = -57; <== WHERE clause so Teradata writes individual TJ entries, even if the functional results are the same (i.e. all rows removed from the table).



Ward Analytics Ltd: Information in motion (www.ward-analytics.com)

  <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