Analytic function have all sorts of uses, and the lag() and lead() functions are among my favourites. I always like to remind people of the sorting overheads involved with analytic functions – but if you are prepared to accept the overhead the simplicity of comparing ‘current’ rows with ‘earlier’ or ‘later’ rows using lag() or lead() is a tremendous coding benefit.
April 9, 2007
November 22, 2006
Consider the following update statement:
update /*+ index(t1 t1_n1) */ t1 set n1 = 0, n2 = n2 + 1, status = 'X' where n1 > 100 and status = 'A' ;