+0 simply prevents Firebird from using an index on the field in question. It can be very useful when you can see that the index will not be very selective in your particular situation (you may have information about your data that isn't available to the optimizer. In my work with cancer data, I know that an index for sex can be very useful if I'm looking for men with breast cancer (<1%), but the same index would be useless if looking for women with the same diagnosis (>99%), the optimizer has no way to know things like this).

Your problem is likely that you have use two indexes for the last table in your PLAN, so try to prevent an index to be used on the one where your criteria is the least selective (it is particularly important if one of the criteria is very selective and the other has very poor selectivity). Another way to solve such a problem, may be to use a combined index on COMP_ID and OTHERPAY_CODE, but my experience is that having lots of combined indexes confuses both me and the optimizer (though that may partly be because I'm on Fb 1.5, Fb 2.0 is better at using combined indexes), so I normally prefer single field indexes.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of rungsant
Sent: 25. oktober 2007 11:45
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Why Adapted Plan "NATURAL" used time less than "INDEX" ?

Thanks for recommend

My Table Key is
--------
CA_OTHER_PAYMENT
- alter table ca_other_payment add constraint pk_ca_other_payment
primary key (comp_id, otherpay_code);
- create index ca_other_payment_idx1 on ca_other_payment
(otherpay_date);
- create index ca_other_payment_idx2 on ca_other_payment
(vendor_code);

CA_OTHER_PAYMENT_SUB
- alter table ca_other_payment_sub add constraint
pk_ca_other_payment_sub primary key (comp_id, otherpay_code,
item_no);
- alter table ca_other_payment_sub add constraint
fk_ca_other_payment_sub_1 foreign key (comp_id, otherpay_code)
references ca_other_payment (comp_id, otherpay_code) on update
cascade;
- create index ca_other_payment_sub_idx1 on ca_other_payment_sub
(item_code);

M_ACC_CHART
- alter table m_acc_chart add constraint pk_m_acc_chart primary key
(comp_id, account_code);
- create index m_acc_chart_idx1 on m_acc_chart (account_name);


why add '+0' ?

I'm try change index ca_other_payment_sub_idx1 to
create index ca_other_payment_sub_idx1 on ca_other_payment_sub
(comp_id, item_code);

it slowly.

Thank.

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@...> wrote:

>
> I would guess that CA_OTHER_PAYMENT_SUB INDEX

(FK_CA_OTHER_PAYMENT_SUB_1,

> CA_OTHER_PAYMENT_SUB_IDX1) is the problem with query 2. Which

of these are the least selective? Try adding '+0' to the least selective of
CA_OTHER_PAYMENT_SUB.COMP_ID AND
CA_OTHER_PAYMENT_SUB.OTHERPAY_CODE and hopefully your query
will speed up.

>
> Another thing is that if COMP_ID is the primary key of

CA_OTHER_PAYMENT, then you would not want to use any other index
on that table. However, I think Fb 2.0 is intelligent enough to figure this
out by itself, so I guess that you have a composite primary key or
something?

>
> HTH,
> Set