--- In firebird-support@yahoogroups.com, "elite705" wrote:

> Hi !
>
> I have the problem with a query, that is very slow.
>
> SELECT *
> FROM ScherenOberstange
> JOIN SchOberFarbe
> ON ScherenOberstange.Farbe_ID = SchOberFarbe.ID
> JOIN V_TextBaustein Farbe
> ON Farbe.TB_ID = SchOberFarbe.Bezeichnung_TB
> WHERE ScherenOberstange.Artikel_ID = 162
>
> Firebird comes up with following plan:
>
> PLAN JOIN (JOIN (JOIN (JOIN (JOIN (FARBE TEXTBAUSTEIN NATURAL,FARBE
> TEXTBAUSTEINVARIANTE INDEX (FK_TEXTBAUSTEINVARIANTE_1)),FARBE
> TEXTBAUSTEINREFSPRACHE INDEX (FK_TEXTBAUSTEINREFSPRACHE_1)),FARBE
> REFSPRACHE INDEX (PK_REFSPRACHE)),FARBE TBVSTATUS INDEX
> (PK_TBVSTATUS)),JOIN (SCHOBERFARBE INDEX
> (FK_SCHOBERFARBE_1),SCHERENOBERSTANGE INDEX (FK_SCHERENOBERSTANGE_1)

))

>
> It goes through the complete view textbaustein instead of using the
> right ID from Farbe.TB_ID.
>
> The View V_Textbaustein is:
>
> CREATE VIEW V_TEXTBAUSTEIN(
> TB_ID,
> TB_BEZEICHNUNG,
> TBV_ID,
> TBV_KURZTEXT,
> TBV_LANGTEXT,
> TBV_SPRACHEID,
> TBV_SPRACHE,
> TBV_STATUSID,
> TBV_STATUS)
> AS
> SELECT TextBaustein.ID ,
> TextBaustein.Bezeichnung,
> TextBausteinVariante.ID,
> TextBausteinVariante.KurzText,
> TextBausteinVariante.LangText,
> RefSprache.ID,
> RefSprache.Bezeichnung,
> TBVStatus.ID,
> TBVStatus.Bezeichnung
> FROM TextBaustein
> 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
> ;
>
> If I change the LEFT JOINS to INNER JOINS there is no problem:
>
> plan:
> PLAN JOIN (SCHERENOBERSTANGE INDEX
> (FK_SCHERENOBERSTANGE_1),SCHOBERFARBE INDEX (PK_SCHOBERFARBE),FARBE
> TEXTBAUSTEIN INDEX (PK_TEXTBAUSTEIN),FARBE TEXTBAUSTEINVARIANTE
> INDEX(FK_TEXTBAUSTEINVARIANTE_1),FARBE TBVSTATUS INDEX
> (PK_TBVSTATUS),FARBE TEXTBAUSTEINREFSPRACHE INDEX
> (FK_TEXTBAUSTEINREFSPRACHE_1),FARBE REFSPRACHE INDEX (PK_REFSPRACHE)

)

>
> I don't understand, why the two LEFT JOINS changes the plan,
> although the corresponding tables RefSprach and TBVStatus are not
> necessary in the query.

Well, left joins do force some tables to be before other tables in the
plan, but like you, I don't understand why it chose the plan it did.

> Any solution? Should I make my own plan, or should I make no use of
> LEFT JOINS in Views?

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.

HTH,
Set