Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 08 Dec 2004 @ 15:45:13 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: Statistics Problems
From:   Anomy Anom

<-- Anonymously Posted: Wednesday, December 08, 2004 10:43 -->

There are a bunch of things you could do if you put version columns in your table, and stored history all in one table.

For the simplest, and probably least functional, approach:

You could just add columns to indicate whether it's a shadow row, an old row, or an unchanged row.


     ColA                 xxx
     ColB                 xxx
     Old_Record_Flag      CHAR(1) COMPRESS ( 'Y', 'N' )
     Shadow_Record_Flag   CHAR(1) COMPRESS ( 'Y', 'N' )

The data:

     ColA     ColB     Old_Record_Flag   Shadow_Record_Flag
     Foo       123            Y                 Y
     Bar       234            Y                 Y
     Mary      456            Y                 Y
     John      789            Y                 N
     John      000            N                 Y

     /* CHANGE 789 to 000 */

Normal views would have:

     WHERE Old_Record_Flag = 'Y';

Shadow views would have:

     WHERE Shadow_Record_Flag = 'Y';

Your web application would have to update the entire record where a record exists with the Shadow_Record_Flag = 'Y' and the Old_Record_Flag = 'Y'. If that does not exist, it would have to insert a new row and update the Shadow_Record_Flag on the existing row to 'N'.

You can accomplish the same kind of thing with version numbers, insert timestamps, etc.

It may not be feasible in a complex model, but the optimizer should at least have at low confidence.

  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016