thanks svein, UNION work like a charm here !
--- In [email protected], Svein Erling Tysvær <svein.erling.tysvaer@...> wrote: > > >Hello, > > > >this is my query : > > > >Select > > MAINTABLE.ID > >from > > MAINTABLE > >Join SECONDTABLE on SECONDTABLE.ID_obj=MAINTABLE.ID_obj > >Join THIRDTABLE on THIRDTABLE.ID_obj=MAINTABLE.ID_obj > >where > > (MAINTABLE.name = 'jean' or > > SECONDTABLE.name = 'jean' or > > THIRDTABLE.name = 'jean') > > > >I have an index on > > > >MAINTABLE.name > >SECONDTABLE.name > >THIRDTABLE.name > > > >the probleme is that firebird use > > > >PLAN JOIN (THIRDTABLE NATURAL, SECONDTABLE INDEX (RDB$PRIMARY436), MAINTABLE > >INDEX (RDB$PRIMARY26)) > > > >and it not use the index of the field > >MAINTABLE.name > >SECONDTABLE.name > >THIRDTABLE.name > > Sorry, that's not possible. Logically, it has to start with one of the tables > with no knowledge about the others and applying that index would only find, > say, 1/3 of the cases. Moreover, unless ID_OBJ is unique, your query might > not return what you want in all cases. If SecondTable contained two 'Jean's > and ThirdTable three 'Jean's for the same ID_obj, that will give you at least > six rows with the same MAINTABLE.ID. What you probably want is a simple UNION > rather than OR: > > SELECT ID > FROM MAINTABLE > WHERE name = 'jean' > UNION > SELECT MT.ID > FROM MAINTABLE MT > JOIN SECONDTABLE ST ON MT.ID_obj=ST.ID_obj > WHERE ST.name = 'jean' > SELECT MT.ID > FROM MAINTABLE MT > JOIN THIRDTABLE TT on MT.ID_obj=TT.ID_obj > WHERE name = 'jean' > > (add ALL to each UNION if you want to include duplicates) > > If things are more complex, you might consider using one or more CTEs (though > it isn't really useful in this simple example): > > WITH MyCTE(ID_OBJ) AS > (SELECT ID_OBJ > FROM MAINTABLE > WHERE name = 'jean' > UNION > SELECT ID_OBJ > FROM SECONDTABLE > WHERE name = 'jean' > SELECT ID_OBJ > FROM THIRDTABLE > WHERE name = 'jean') > > SELECT M.ID > FROM MAINTABLE M > JOIN MyCTE CTE ON M.ID_OBJ = CTE.ID_OBJ > > HTH, > Set >
