Hi,

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

Thanks

William


Svein Erling Tysv�r <svein.erling.tysvaer@...> wrote: 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






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

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! Autos. Looking for a sweet ride? Get pricing, reviews, & more on new and used cars.

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