Well, William, you're not guaranteed that +0 will change to the right
plan, you just prevents the optimizer from choosing the plan it thinks
is the best. You have an index on INVOICE.CLIENT_ID that the optimizer
wants to use (it thinks the benefit of using this index outweighs the
cost of going natural on CONTACTS), but you know that it a bad choice.
INVOICE.CLIENT_ID+0 isn't indexed, and the optimizer can no longer use
the INVOICE.CLIENT_ID index at all.

With this index 'disabled', the optimizer doesn't benefit from having
the INVOICE table after the CLIENTS table in your plan. But it knows
that by putting it before the CLIENTS and CONTACTS table can get away
from the NATURAL on the CONTACTS table that it doesn't like too much.

So there is no more magic to +0 than to prevent one index from being
used. The magic is the optimizer that Arno maintains. It normally does
the right thing in the first attempt, but when it doesn't, it normally
does the right thing once you add +0 or ||'' in the correct place(s).

Set

--- In firebird-support@yahoogroups.com, Wei Yu wrote:
> Hi,
>
> Thanks, much appreicated. My only question: what/why will the the
> optimizer change to the right index after adding + 0? + 0 is
> really a magic, why?
>
> Thanks
>
> William
>
> Svein Erling Tysvaer wrote:
>
> 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