My mapper joins the parent classes' tables to the current class' table in
the view. In the ShapeView only ID, X, and Y is selected from the shape
table, and none of the child tables are touched, opposite to your sample.
But even though all Shape objects (circles and rectangles too) are in the
resultset as Shape objects. I see this storage model quite consistent.
You are right, that this can be done with inner join too, this is an option
in the mapper. Oracle and MSSQL performs this left join optimization, so it
is usually used with left join by other mapper users. I have asked them (the
developers of the mapper) to perform this optimization at mapper level
because not all DBMSs supported this optimization, but it seemed they didn't
like this idea... And then I came here. This optimization would be useful
for every Postgres users.

To be honest I also find your sample strange, more exactly that
*sibling* child tables are left joined to the parent. Maybe because the
storage model is different than in my mapper.

In my case the left joined parent tables should be excluded by the optimizer
if possible.

Best regards,

2007/4/8, Nicolas Barbier <[EMAIL PROTECTED]>:

2007/4/7, Ottó Havasvölgyi <[EMAIL PROTECTED]>:

> My simple example:
> Class hierarchy and fields:
> Shape (ID, X, Y)
> |
> +-Circle (ID, Radius)
> |
> +-Rectangle (ID, Width, Height)
> The mapper creates 3 tables with the columns next to the class name.
> And it creates 3 views. One of them:
> RectangleView:  SELECT r."ID" as "ID", s."X" as "X", s."Y" as "Y",
> as "Width", r."Height" as "Height" FROM "Rectangle" r LEFT JOIN "Shape"
s ON
> ( r.ID=s.ID)

I find this view definition a bit strange: why is there a left outer
join? I expect there to be a FK from Rectangle.ID to Shape.ID ("all
rectangles are shapes"), which makes the definition totally equivalent
with one in which a normal join is used (whether attributes of Shape
are used or not).

The main use case I see for the original optimization is ORMs that
join in a whole hierarchy, even when only a part of it is needed. I
guess that that is rather common. The ORM that I use does exactly
this, because the main target-DBMSs (MS-SQL and Oracle) do the
optimization for it.

Example (somewhat less contrived than my previous one):

Imagine an implementation of the typical "books that are borrowed by
people" n-m relationship, using three tables ("Book", "Borrowed",
"Person"). Let's find all books that have been borrowed by a certain

The "non-ORM" version would be something like:

   JOIN Borrowed ON Borrowed.book_id = <>
WHERE Borrowed.person_id = <x>;

Now assume that Borrowed is a class hierarchy mapped into multiple
tables by a typical ORM. The query would probably become something

        JOIN Borrowed_Parent ON Borrowed_Parent.book_id =<>
   LEFT JOIN Borrowed_Child1 ON =
   LEFT JOIN Borrowed_Child2 ON =
WHERE Borrowed_Parent.person_id = <x>;

It is clear that the children of the hierarchy are needlessly joined
in (as the only attribute that is actually needed is person_id, which
is on the parent level). It is not always trivial for the ORM to find
that out, without writing stuff that looks suspiciously similar to a
DBMS optimizer.

Maybe it is debatable whether this optimization should be done by the
application (i.e. the ORM) or by the DBMS. I am personally in favor of
doing it in the DBMS.


Nicolas Barbier

Reply via email to