Re: Three table database - period (?)

Vadim Tropashko wrote:
> In article <958lej$31i$1_at_news.tue.nl>,
> hidders_at_win.tue.nl (Jan Hidders) wrote:
> > Vadim Tropashko wrote:
> > >
> > > Performance implications are probably much more severe than just
> > > more joins. In traditional schema, for example, I could create
> > > composite key index -- what do I do in metamodel?
> >
> > Nothing, you just define the one index on every attribute. :-) But
> > that is "only" going to slow things down with a factor n where n is
> > the number of attributes in the composite key.
>
> Well, if I have FirstName and LastName attributes and asking for a
> ResultSet where FirstName='John' and LastName='Doe' in metamodel
> approach it could be that index on String attribute is not selective
> enough to filter either 'John' value, or 'Doe'. At the same time,
> optimizer might find that composite key FirstName+LastName is very
> selective in traditional schema. In first case, we do full table scan,
> therefore, query speed is proportional to the size of the table.

No, that is not neccessarily correct. A full table scan may not be neccessary because the index gives you a small subset of blocks which are the only one's you need to retrieve.

Moreover, you could even involve the index on the other attribute by retrieving which set of blocks *it* says you have to retrieve, and then determine the intersection of the two sets. It is not unlikely that the combination is just as selective as a combined index. But I doubt if most query optimizers can actually use this trick.

-- 
  Jan Hidders