Archives of the TeradataForum

Message Posted: Wed, 01 Dec 2004 @ 20:30:02 GMT

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

Clay,

why not adding a shadow columns to the 'real' table for all columns which can be changed by the user/application.

Defining them with compression shouldn't cause too much overhead if the table has not hundred of columns and billions of rows.

Hide these columns from the =B4normal=A1 user via a view and define a current view via the coalesce function.

No join at all would be required with the same functionality.

What I mean should look like:

     Create table a

     ( PK_A integer,
     COL_A integer,
     COL_A_SHADOW integer,
     COL_B VARCHAR(12),
     COL_B_SHADOW VARCHAR(12)
     ) unique primary index ( PK_A );


     create view a_normal

     as

     select PK_A,
     COL_A,
     COL_B

     from a;


     create view a_CURRENT
     as
     select PK_A,
     coalesce(COL_A_SHADOW,COL_A) as col_A,
     coalesce(COL_B_SHADOW, COL_B) as col_B

     from a;

The application is 'only' allowed to change the shadow tables.

Kind regards

Ulrich

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