In DBW, if you bring up the menu on the root of the database's schema
tree, you can select Recompute Index Selectivity which will affect the
indexes that back the constraints.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Wei Yu
Sent: Thursday, February 09, 2006 2:51 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Big Index using problem in FB

Hi, Alex

Thanks much!. The "inner join CONTACTS a on a.ID = c.CONTACT_ID + 0"
does the trick. Here is my questions:

1)How to update indices statistics, I'm using DBW, IBExpert.

2)Is there any Docs/Articles/tips regarding how to optimize SQLs out
there?

Best Regards,

William

Alexandre Benson Smith <iblist@...> wrote:
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



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

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


---------------------------------
Brings words and photos together (easily) with PhotoMail - it's free
and works with Yahoo! Mail.

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



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

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

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

Yahoo! Groups Links