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
>

William,

I am not an optimizer guru, so take my comments with care.

left join force the tables before the LEFT JOIN to be treated first in
the join order, when you have only INNER JOINS the order could be
changed acording to the optimizer guesses.

I'd expect INVOICE to be the first table because it has a filter clause
on it, but optimzer could use CONTACTS first if it is a small table. If
it is not the case, perhaps you should update your indices statistics.
you could change your join clause to:

inner join CONTACTS a on a.ID = c.CONTACT_ID + 0 and

this could help to avoid that index.

using LEFT JOIN is a trick to help optimizer choose a better plan in
some cases, as adding +0 or || '' to the fields

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br