Hi William!

I would expect the plan of this query to be something like

JOIN SORT( JOIN( JOIN( JOIN( JOIN(i INDEX(INVOICE_DATE_IDX), c
INDEX(PK_CLIENTS)), a INDEX(PK_CONTACTS)), d INDEX(PK_DIVISION)), cd
INDEX(CLIENT_DIVISION_CLIENT_ID_IDX)))

or alternatively use the division_id-index on the client_division
table (or a combined index or both indexes).

If you provided us with the complete plan, rather than just parts of
it, it would be easier to answer. Similarly, it would be nice to know
which indexes are available for the fields referred to by this query,
and some information about the selectivity for the fields in question
and the size of the tables.

In most cases, it is a bad idea to use NATURAL when there is an index
available, but the optimizer for some reason chose it was the best
thing to do. With more information maybe we can try to deduce why it
chose this.

Oh, I just notice that you wrote:

> If I changed the above 'inner join CLIENTS c on c.ID = i.CLIENT_ID'
> to 'left join CLIENTS c on c.ID = i.CLIENT_ID'
> then suddenly the PLAN changed to C PK_CLIENT, A PK_CONTACT, why?

This indicates that your original plan did not have INVOICE i as the
first table in the plan. This is probably the source of your problem.
Is there no index (or multiple indexes, that is a good way to confuse
the optimizer) for INVOICE_DATE or REVERSEID and if there are, how are
their selectivity? The plan changed simply because you forced the
optimizer to put INVOICE before CLIENTS in the plan, and that makes it
possible to use different indexes for a and c. Why didn't it do that
voluntarily?

Set

--- In firebird-support@yahoogroups.com, "william_yuwei" wrote:
> Hi, All
>
> I have the following SQL:
>
> select i.INVOICE_DATE, i.INVOICE_NO, i.SHIPPING_CHARGE,
> i.DIVISION_ID, a.ACCOUNT_NO, a.NAME, d.NAME,
> i.KIND, i.SHIPPING_GST + i.GST, i.NETSALES,
> cd.PAYMENT_DISCOUNT
> from INVOICE i
> inner join CLIENTS c on c.ID = i.CLIENT_ID
> inner join CONTACTS a on a.ID = c.CONTACT_ID and
> (:clientgroupid <= 0 or a.CLIENTGROUP_ID = :clientgroupid)
> inner join DIVISION d on d.ID = i.DIVISION_ID
> inner join CLIENT_DIVISION cd on cd.CLIENT_ID = i.CLIENT_ID and
> cd.DIVISION_ID = i.DIVISION_ID
> where i.INVOICE_DATE between :startdate and :enddate and
> i.POSTED = 'Y' and i.REVERSEID is null
> order by i.DIVISION_ID, i.INVOICE_NO
>
> The Plan for above SQL: A NATURAL, C FK_CONTACT_ID, why is that? I
> suppose that A PK_CONTACT, and C PK_CLIENT
>
> If I changed the above 'inner join CLIENTS c on c.ID = i.CLIENT_ID'
> to 'left join CLIENTS c on c.ID = i.CLIENT_ID'
> then suddenly the PLAN changed to C PK_CLIENT, A PK_CONTACT, why?
>
> it looks to me that Plan for left join is better than inner join,
> how does that happen?
>
> Thanks
>
> William