Huh??? I thought I_TO6PCID_A was an ascending index on
TURNOVER_PC_CS.TO6CSID. What index is I_TO6ALL_A_U? What table do
CSUSID1 and CSGPID3 belong to and are any of them indexed?

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'm confused...
Set

Guido Klapperich wrote:

>> Hi again, Guido!
>
> Hi Svein,
>
> I have been away for a couple of days.
>> Seems like Fb 2.0 thinks it is better off to consider TURNOVER_PC before
>> CUSTOMERS. Try changing to
>>
>> select BLPCID,sum(BLGROSS_SALES),sum(BLRETURNS),sum(BLNETSALES),
>> sum(BLDELIVERY_QUANTITY),sum(BLBACKLOG) from BACKLOG
>> where BLCSSALESREPID=56 and BLCSGPID3=65 and
>> not exists (select * from TURNOVER_PC_CS join CUSTOMERS on
>> TO6CSID=CSID+0 where CSUSID1=56 and CSGPID3=65 and BACKLOG.BLPCID=CSID)
>> group by BLPCID
>
> Yes it changes a lot, but it's getting worse.
> Here's the plan:
> PLAN JOIN (TURNOVER_PC_CS INDEX (I_TO6PCID_A), CUSTOMERS INDEX
> (CUSTOMERS_IDX1))
> PLAN (BACKLOG ORDER I_BLPCID_BLMCID_A INDEX
> (I_BLCSGPID3_BLPCPAID_BLPCMPID_A, BACKLOG_IDX1))
>
> and the reads
> BACKLOG 216
> CUSTOMERS 11.828.315 IR
> TURNOVER_PC_CS 11992 IR
>
>> I wouldn't bother about the change in the plan for BACKLOG, I think that
>> is due to Fb 2.0 telling us more about what happens in the background,
>> something that Fb 1.5 just kept as a secret.
>>
>> By the way, do you have several indexes that starts with the fields
>> TURNOVER_PC_CS.TO6CSID or CUSTOMERS.CSID? If so, maybe that could
>> confuse the optimizer?
>
> No
>
> Regards
>
> Guido