Hi again, Guido!

I was guilty of reading your original query to quickly, I thought
TO6PCID and TO6CSID was the same field, hence I switched to CSID in the
WHERE clause.

First, you do have at least one irrelevant index, there is no need for a
separate index on any field that is the first field of a composite index
(at least, I've never heard anyone claim that such an index did any
good). So just delete I_TO6PCID_A and all other indexes that you may
have that is only the first field(s) of another index (i.e. delete the
index on (a) if you also have an index on (a, b), and delete the index
on (a, b) if you have an index on (a, b, c). (please, someone correct me
if I'm wrong).

Then check the plan again in both Firebird 1.5 and Firebird 2.0. I don't
think it changes anything, but it is worth trying.

Then, let's look at the ON and WHERE clause of your subselect to try
making it use the same plan as Firebird 1.5 chose (I think the outer
select is identical, just a matter of Firebird 2.0 being more 'verbose'
than Firebird 1.5 in displaying the plan). I'll add aliases to the
tables to make it easier for myself (B, C and TPC):

on TPC.TO6CSID=C.CSID where C.CSUSID1=56 and C.CSGPID3=65 and
B.BLPCID=TPC.TO6PCID

Hmm, we have to do something to make CUSTOMER more attractive than
TURNOVER_PC_CS. If TO6CSID is pretty selective and you do have an index
which starts with this field, then you can of course just try
B.BLPCID=TPC.TO6PCID+0. If not, well, I've never tried, but what happens
if you do AND (B.BLPCID=TPC.TO6PCID or C.CSUSID1=-10) //-10 has to be a
value that doesn't exist and CSUSID1 has to be the first field of an
index. As I said, I've never tried anything like it, but if I was an
optimizer and saw such a condition, I would reconsider which table to
index first. Hopefully, it will not ignore the index altogether as it
does if using +0 or a non-indexed field.

As for your LEFT JOIN query, you have to use LEFT JOIN CUSTOMERS and not
just JOIN CUSTOMERS, and try to put CUSTOMERS before TURNOVER_PC_CS (if
you want a plan similar to what Fb 1.5 came up with). Try something like:

select B.BLPCID,sum(B.BLGROSS_SALES),sum(B.BLRETURNS),sum(B.BLNETSALES),
sum(B.BLDELIVERY_QUANTITY),sum(B.BLBACKLOG)
from BACKLOG B
left join CUSTOMERS C on C.CSUSID1=56 and C.CSGPID3=65
left join TURNOVER_PC_CS TPC
on TPC.TO6CSID=C.CSID and B.BLPCID=TPC.TO6PCID
where TPC.TO6PCID is null
group by B.BLPCID

Nope, this doesn't work. If there are several customers which matches
whereas TURNOVER_PC_CS doesn't, then they will match your where criteria
and the sums will be wrong.

Maybe (not tested, may not give the correct result or even give an error)

select B.BLPCID,sum(B.BLGROSS_SALES),sum(B.BLRETURNS),sum(B.BLNETSALES),
sum(B.BLDELIVERY_QUANTITY),sum(B.BLBACKLOG)
from BACKLOG B
left join (CUSTOMERS C on C.CSUSID1=56 and C.CSGPID3=65
join TURNOVER_PC_CS TPC on TPC.TO6CSID=C.CSID and B.BLPCID=TPC.TO6PCID)
where C.CSID is null
group by B.BLPCID

I'm a bit too tired to come up with something better. I recommend trying
to stick with the NOT EXISTS.

HTH,
Set

Guido Klapperich wrote:

> Hi Svein,
>
> now I have more time to answer you.
>
>> Huh??? I thought I_TO6PCID_A was an ascending index on
>> TURNOVER_PC_CS.TO6CSID.
> No I_TO6PCID_A is an ascending index on TURNOVER_PC_CS.TO6PCID. If it's
> not to long, then the name of the index consists of the column names,
> that are indexed.
>
> What index is I_TO6ALL_A_U?
> It's an index on TURNOVER_PC_CS.TO6PCID and TURNOVER_PC_CS.TO6CSID
>
> What table do
>> CSUSID1 and CSGPID3 belong to and are any of them indexed?
> They belong to CUSTOMERS and they are indexed with the index CUSTOMERS_IDX1.
>
>> If I_TO6PCID_A is the index I believe it to be, I don't understand how
>> it is possible to use the plan you report with my changes (I would have
>> understood it if you had forgotten to change to 'BACKLOG.BLPCID=CSID'
>> and kept 'BACKLOG.BLPCID=TO6CSID').
> I thought it has been a typo from you, therefore I didn't mention it,
> but the correct where condition should be BACKLOG.BLPCID=TO6PCID.
>
> Regards
>
> Guido