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 Tysv�r <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





++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++




SPONSORED LINKS
Technical support Computer technical support Compaq computer technical support Compaq technical support Hewlett packard technical support Microsoft technical support

---------------------------------
YAHOO! GROUPS LINKS


Visit your group "firebird-support" on the web.

To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


---------------------------------





William, Yu


---------------------------------
Yahoo! Mail
Use Photomail to share photos without annoying attachments.

[Non-text portions of this message have been removed]