> 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
>
> Here is the PLAN:
>
> SORT (JOIN (JOIN (A NATURAL,C INDEX (FK_CLIENTS_CONTACTID),
> I INDEX (FK_INVOICE_CLIENTID,IDX_INVOICE_INVOICEDATE),
> D INDEX (PK_DIVISION)),CD INDEX (FK_CLIENT_DIVISION_CLIENTID)))
>
> and Yes, I do have the index on INVOICE.RESERVEID

OK, this means that the optimizer believes that using an index on
INVOICE.CLIENT_ID is more important than using any index on CONTACTS.
ID. Since it also may use indexes on INVOICE.INVOICE_DATE and INVOICE.
REVERSEID (or RESERVEID - I hope these aren't two different fields?),
it seems probable that the optimizer makes a bad choice.

Let's start by preventing the possibility of using the INVOICE.
CLIENT_ID index, and see if that is enough to improve performance:

inner join CLIENTS c on c.ID = i.CLIENT_ID+0

The resulting plan may well be the same as when you did

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

INVOICE is the only table in the WHERE clause, so it is the only table
that may be first in the plan and still use an index. Hence, I just
find it easier to understand CLIENT_ID+0, than the more indirect
CONTACT_ID+0.

HTH,
Set