Re: update == delete + insert?

On 3/20/06, Craig A. James <cjames@modgraph-usa.com> wrote:
> I've seen it said here several times that "update == delete + insert".  On the other hand, I've noticed that "alter
table[add|drop] column ..." is remarkably fast, even for very large tables, which leads me to wonder whether each
column'scontents are in a file specifically for that column. 
>
> My question: Suppose I have a very "wide" set of data, say 100 columns, and one of those columns will be updated
often,but the others are fairly static.  I have two choices: 
>
> Design 1:
>    create table a (
>      id integer,
>      frequently_updated  integer);
>
>    create table b(
>      id integer,
>      infrequently_updated_1 integer,
>      infrequently_updated_2 integer,
>      infrequently_updated_3 integer,
>      ... etc.
>      infrequently_updated_99 integer);
>
> Design 2:
>    create table c(
>      id integer,
>      frequently_updated  integer,
>      infrequently_updated_1 integer,
>      infrequently_updated_2 integer,
>      infrequently_updated_3 integer,
>      ... etc.
>      infrequently_updated_99 integer);
>
> If "update == delete + insert" is strictly true, then "Design 2" would be poor since 99 columns would be moved around
witheach update.  But if columns are actually stored in separate files, the Designs 1 and 2 would be essentially
equivalentwhen it comes to vacuuming. 
>
> Thanks,
> Craig
>

design 1 is normalized and better
design 2 is denormalized and a bad approach no matter the RDBMS

update does delete + insert, and vacuum is the way to recover the space

--
Atentamente,
Jaime Casanova

"What they (MySQL) lose in usability, they gain back in benchmarks, and that's
all that matters: getting the wrong answer really fast."
                           Randal L. Schwartz