Svein Erling Tysvær schrieb:

> Writing your own plan is possible, and you could avoid left joins in
> views (I don't use left joins enough to give you any useful advice).
> Though I'd simply do this little change in your query to hint the
> optimizer:
>
> SELECT *
> FROM ScherenOberstange
> JOIN SchOberFarbe
> ON ScherenOberstange.Farbe_ID = SchOberFarbe.ID
> JOIN V_TextBaustein Farbe
> ON Farbe.TB_ID = SchOberFarbe.Bezeichnung_TB+0
> WHERE ScherenOberstange.Artikel_ID = 162
>
> > If any other information is needed, please ask!
>
> I would expect my suggestion above to fix your current problem, but it
> would be nice to know whether I'm right or not.
>

Thank you for your answer.

I tried your solution, but it had no effect. With Left Join the plan is
not optimal and with full join it is as expected. I also tried to copy
the Joins of the view into my query:

SELECT *
FROM ScherenOberstange
JOIN SchOberFarbe
ON ScherenOberstange.Farbe_ID = SchOberFarbe.ID
JOIN TextBaustein Farbe
ON Farbe.ID = SchOberFarbe.Bezeichnung_TB
JOIN TextBausteinVariante
ON TextBausteinVariante.TextBaustein_ID = TextBaustein.ID
LEFT JOIN TextbausteinRefSprache
ON TextbausteinVariante.ID =
TextbausteinRefSprache.TextbausteinVariante_ID
LEFT JOIN RefSprache
ON TextbausteinRefSprache.RefSprache_ID = RefSprache.ID
LEFT JOIN TBVStatus
ON TextbausteinVariante.Status = TBVStatus.ID
WHERE ScherenOberstange.Artikel_ID = 162

Now the plan is also correct. It seems that only the combination of Left
Join and View is producing a bad plan. So I have the solution to not use
the view at all, although it makes query-building a lot more complex. We
are using Firebird-1.5.1.4481 and I don't want to update right now,
because the system has to function for the next two weeks.