--- In firebird-support@yahoogroups.com, "Svein Erling" <svein.erling.tysvaer@...> wrote:

>
> Hi!

Hi Svein, thanks again for share your knowledge and wisdom :D

>
> I've no idea why things are duplicated in your first plan, and I don't think I like your second query due to it not yielding any result if any of the parts should return NULL (that is, the parts the first query covers for using COALESCE).

yep, indeed I've forgot to change the second query to "Left joins" before the subselects. But noted that after post the thread...
I was thinking that this could help the optimizer (anyway, lefting some of the results out of the select.).
Now I changed it, and the new plan is:

PLAN JOIN (JOIN (JOIN (P NATURAL, SORT (JOIN (SAI SPT INDEX (SAIDAPRODUTOS_IDX3), SAI SNTT INDEX (RDB$PRIMARY29)))), SORT (JOIN (ENTRA EPT INDEX (RDB$FOREIGN55), ENTRA ETT INDEX (RDB$PRIMARY17)))), DEVOLVE DVP ORDER RDB$FOREIGN51 INDEX (RDB$FOREIGN51))

This is what I was expecting in plan when creating the query.

>However, I do have a third suggestion for you that I think looks >simpler:

(text truncated)

>
> Unfortunately, I have no idea about performance, WITH is a fairly new construct in Firebird, and I still spend most of my Firebirdy time with Firebird 1.5...
>
> HTH,
> Set
>

I will test your suggestion, and think that will help. It really looks simpler. Unfortunately, I'm still learning CTE's querys...

many thanks, again.
regards