Re: SQL for Modelling Generalization Hierarchy

"Robert Brown" <robertbrown1971_at_yahoo.com> wrote in message news:240a4d09.0405270839.16582e0b_at_posting.google.com...
> The only solution I found for this is a generalization hiearchy where
> you have the employee table with all generic attributes and then you
> add tables for each new employee subtype as necessary. The subtype
> tables share the primary key of the employee table. The employee table
> has a "discriminator" field that allows you to figure out which
> subtype table to load for a particular entity.
>
> This solution does not seem to scale since for each value of
> "discriminator" I need to perform a join with a different table. What
> if I need to retrieve 1,000 employees at once. Is that possible with
> SQL or do you I need to iterate look at the discriminator and then
> perform the appropriate join? If this kind of iteration is necessary
> then obviously this generalization hierarchy approach does not work
> since it would be painfully slow.

Urban legend. If your result set is small then indexed nested loops join is fast no matter how many joins are involved. In a typical scenario

select * from Employees e, Doctors d
where e.id = d.id
and e.name = 'Tegiri Nenashi'

the RDBMS fetches small list of Employees by index range scan (several block reads) and, then, for each Employee record found it would fetch Doctors subtype record by index unique scan (several block reads as well). Received on Thu May 27 2004 - 18:56:12 CEST