Re: Three table database - period (?)

Jens wrote:
> Jan Hidders wrote:
> >
> > So, although in the first case you have retrieved information that you
> > don't really need, the number of disk reads is still lower.
>
> Isn't this only true if you have all the relevant information in
> sequential blocks on the disk?

No, I didn't assume anything about the sequence of the blocks. But what I did assume was that if the entitiy attributes are distributed over several tables then they will probably all be on different blocks.

> If they are spread out all over the disk, as is likely the case if
> you add attributes later, you will end up with multiple seeks anyway.

Yes, but that is what I gave as the second case. In the first case (all attributes in 1 record) you have to reorganize your database anyway if you add new attributes.

> Maybe the database can compensate for this somehow.

It might by clustering records of different tables in the same block if they belong together. Although I know of quite some research that has been done on this (look in the proceedings of VLDB) I don't think that there are many DBMSs that actually implement this. But I am not really familiar enough with actual implementations to say this for sure.

-- 
  Jan Hidders