Re: Optimising "full outer join where" for muti-row to multi-column view

On Thu, Dec 28, 2006 at 08:29:56PM +0000, Phil Endecott wrote:
> To try and avoid this, I tried using a full outer join in the view 
> definition.  In this case the row would always be present in the view, 
> even if the data for the other columns were not present.  I hoped that 
> the query would then be optimised to look up only the orientation 
> information:



You really want a left outer join, not a full outer (is the full outer
join even producing the answer you expect?). A full outer join can
never be optimised away.

That said, I don't know if the logic exists to optimise away a left
join either. It would only be possible if the join were on the primary,
thus you could be assured of exactly one row.

The only other alternative would be to make each column a subselect
instead. If you don't take that column as output, maybe it optimises
the subquery out entirely?

Like so:

create view view photo_info_v as
select photo_id,
  (select orientation from e2 where e2.tag='Orientation' and e2.photo_id = e1.photo_id), 
etc

Have a nice day,
-- 
Martijn van Oosterhout      http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.