2007/4/7, Andreas Pflug <pgadmin@pse-consulting.de>:
> Tom Lane wrote:
>
>> "Ottó Havasvölgyi" <havasvolgyi.otto@gmail.com> writes:
>>
>>> When using views built with left joins, and then querying against these
>>> views, there are a lot of join in the plan that are not necessary, because I
>>> don't select/use any column of each table in the views every time. Tables
>>> that are left joined and never referenced anywhere else in the query should
>>> be removed from the plan.
>>
>> That might cause you to get the wrong number of copies of some rows ---
>> what if a row of the left table should join to multiple rows on the right?
>
>That would be trouble. But I've seen quite some cases where the right
>can contain only zero or one row, because of PK constraints. In this
> case, elimination would be safe.
I would like to mention that this kind of structure is used by
Hibernate (ORM for Java/.NET) for mapping class hierarchies. I can
attest that this optimization is supported by MS-SQL and I think (not
tested) also by Oracle.
To recapitulate, the optimization would be: Remove left outer joined
tables from the join list, if they are not used by the query, and the
join attributes are a key for it (I assume an equality join).
Typical example:
PARENT_CLASS (PK: ID)
CHILD_CLASS (PK: ID)
In query:
SELECT P.ID
FROM PARENT_CLASS P LEFT OUTER JOIN CHILD_CLASS C ON P.ID = C.ID;
the join on CHILD_CLASS can be eliminated, because the join attribute
ID is a key for it, and none of its attributes are used in the query.
Hibernate:
<url:http://www.hibernate.org/>
Hibernate Inheritance Mapping:
<url:http://www.hibernate.org/hib_docs/reference/en/html/inheritance.html>
greetings,
Nicolas
--
Nicolas Barbier
http://www.gnu.org/philosophy/no-word-attachments.html