For the health and wealth part visit www.nofat.co.za or www.healthycash.co.za

On the Oracle side, here is a nice example of how to use lead/lag finctions to update redundant intra table previous/next row pointers:

declare
  n pls_integer :=1;
begin

  FOR c IN (select * from
               (
                  select p.CLIENT_CUR_KEY, p.RECORD_START_DATE, P.RECORD_STOP_DATE
                  ,lead(client_key,1)
                  over
                  (
                    partition by CLIENT_CUR_KEY order by RECORD_START_DATE
                  ) as ACTUAL_NEXT_CLIENT_KEY, p.NEXT_CLIENT_KEY
                  ,
                   lag(client_key,1)
                              over
                              (
                                partition by CLIENT_CUR_KEY order by RECORD_START_DATE
                              ) as ACTUAL_prev_CLIENT_KEy           
                  ,
                  p.CLIENT_KEY, p.PREV_CLIENT_KEY , p.EDW_LOAD_KEY
                  ,rowid
                  from edw_p.dim_pers_detail p
               )
            where   
              nvl(ACTUAL_NEXT_CLIENT_KEY,-1) != nvl(CLIENT_KEY,-2)
                or nvl(ACTUAL_prev_CLIENT_KEY,-1) != nvl(prev_CLIENT_KEY,-2)

  ) LOOP
 
    if c.ACTUAL_NEXT_CLIENT_KEY is not null then
      UPDate   edw_p.dim_pers_detail set
        NEXT_CLIENT_KEY = c.ACTUAL_NEXT_CLIENT_KEY
      where
        rowid = c.rowid
        and
         nvl(NEXT_CLIENT_KEY,-1) != c.ACTUAL_NEXT_CLIENT_KEY;
    end if;    
      
    if c.ACTUAL_prev_CLIENT_KEY is not null then
   
     UPDate edw_p.dim_pers_detail set
            PREV_CLIENT_KEY = c.ACTUAL_prev_CLIENT_KEy
          where
            rowid = c.rowid
            and
            nvl(PREV_CLIENT_KEY,-1) != c.ACTUAL_prev_CLIENT_KEY;
           
    end if;        
            

    n:=n+1;

    if n>1000 then
      commit;
      n:=1;
    end if;

  END LOOP;
 
end; 
/
commit;  

Herman Scheepers signing out for now 

Oracle expert performance tuning DBA and architect