2007/4/7, Andreas Pflug <[EMAIL PROTECTED]>:
Tom Lane wrote:
"=?ISO-8859-1?Q?Ott=F3_Havasv=F6lgyi?=" <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
