On 3/20/06, Craig A. James <[EMAIL PROTECTED]> 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's contents 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 with each update. But if columns
> are actually stored in separate files, the Designs 1 and 2 would be
> essentially equivalent when 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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
