You could try something like

SELECT
BT.B_KOD,
BT.P_KOD,
BT.DATUM,
BT.ROGZITVE,
BT.OSSZEG,
BT.EGYOSSZEGU,
BT.MEGJEGYZES,
PT.NEV,
PT.P_KOD,
PT.TARSULAT_KOD,
TT.NEV,
TT.TARSULAT_KOD
FROM BEFIZETES_TORZS BT
JOIN PARTNER_TORZS PT ON BT.P_KOD=PT.P_KOD
JOIN TARSULAT_TORZS TT ON PT.TARSULAT_KOD+0=TT.TARSULAT_KOD
ORDER BY BT.DATUM

to prevent PARTNER_TORZS_IDX2 from being used. It will get a different
plan, but I don't know what it will look like. And I'm uncertain whether
the 'wrong' or 'good' plan is the best for retrieving all your records.
Sure, the 'good' plan should retrieve the first record quicker, but have
you timed getting all records to your client?

In general, I'd say SELECT statements without a WHERE clause ought to be
thoroughly investigated to ascertain that the result set only contains
relevant records. And your original query only had things belonging to
the JOIN clause in your WHERE criteria since you used SQL-89 rather than
SQL-92 syntax.

HTH,
Set

rapdirector wrote:

> Hi all!
>
> I have the following tables:
>
> ------------------------------------------------
> CREATE TABLE BEFIZETES_TORZS (
> B_KOD LONG_INTEGER NOT NULL,
> P_KOD LONG_INTEGER,
> DATUM TS,
> ROGZITVE TS,
> OSSZEG MONEY_18_2,
> EGYOSSZEGU BOOLEAN COLLATE PXW_HUNDC,
> MEGJEGYZES MEMO);
>
> ALTER TABLE BEFIZETES_TORZS ADD PRIMARY KEY (B_KOD);
>
> CREATE INDEX BEFIZETES_TORZS_IDX1 ON BEFIZETES_TORZS (P_KOD);
> CREATE INDEX BEFIZETES_TORZS_IDX2 ON BEFIZETES_TORZS (DATUM);
> CREATE INDEX BEFIZETES_TORZS_IDX3 ON BEFIZETES_TORZS (OSSZEG);
> CREATE DESCENDING INDEX BEFIZETES_TORZS_IDX4 ON BEFIZETES_TORZS (DATUM);
> CREATE INDEX BEFIZETES_TORZS_IDX5 ON BEFIZETES_TORZS (ROGZITVE);
>
> ------------------------------------------------
>
> CREATE TABLE PARTNER_TORZS (
> P_KOD LONG_INTEGER NOT NULL,
> NEV CHAR_70 COLLATE PXW_HUNDC,
> TSZAM CHAR_15 COLLATE PXW_HUNDC,
> TARSULAT_KOD SMALL_INTEGER,
> PERIOD_KOD SMALL_INTEGER,
> STATUSZ_KOD SMALL_INTEGER,
> EGYEB_KOD SMALL_INTEGER,
> T_KOD SMALL_INTEGER,
> IRSZ CHAR_6 COLLATE PXW_HUNDC,
> VAROS CHAR_70 COLLATE PXW_HUNDC,
> UTCA_HSZ CHAR_70 COLLATE PXW_HUNDC,
> LEV_IRSZ CHAR_6 COLLATE PXW_HUNDC,
> LEV_VAROS CHAR_70 COLLATE PXW_HUNDC,
> LEV_UTCA_HSZ CHAR_70 COLLATE PXW_HUNDC,
> OSSZ_FIZ_KOT MONEY_10_2,
> RESZLET MONEY_10_2,
> TAMOGATOTT BOOLEAN COLLATE PXW_HUNDC,
> HELYRAJZI_SZAM CHAR_30 COLLATE PXW_HUNDC,
> DELETED BOOLEAN COLLATE PXW_HUNDC,
> BEFIZ_OSSZ MONEY_18_2,
> TARTOZAS MONEY_18_2,
> FELSZOLITASOK SMALL_INTEGER,
> BEF_KEZD TS,
> MEGJEGYZES MEMO);
>
> ALTER TABLE PARTNER_TORZS ADD PRIMARY KEY (P_KOD);
>
> CREATE INDEX PARTNER_TORZS_IDX1 ON PARTNER_TORZS (NEV);
> CREATE INDEX PARTNER_TORZS_IDX2 ON PARTNER_TORZS (TARSULAT_KOD);
> CREATE INDEX PARTNER_TORZS_IDX3 ON PARTNER_TORZS (TAMOGATOTT);
> CREATE UNIQUE INDEX PARTNER_TORZS_IDX4 ON PARTNER_TORZS (TSZAM,
> TARSULAT_KOD);
> CREATE INDEX PARTNER_TORZS_IDX5 ON PARTNER_TORZS (PERIOD_KOD);
> ----------------------------------------------------------------
>
> CREATE TABLE TARSULAT_TORZS (
> TARSULAT_KOD SMALL_INTEGER NOT NULL,
> JOGUTOD SMALL_INTEGER,
> KOZMU_KOD SMALL_INTEGER,
> NEV CHAR_80 COLLATE PXW_HUNDC,
> DEF BOOLEAN COLLATE PXW_HUNDC,
> ALAPITVA TS,
> MEGSZUNTETVE TS,
> BEFIZ_KEZDETE TS,
> BEFIZ_VEGE TS,
> FUTAMIDO SMALL_INTEGER,
> TAGOK_SZAMA SMALL_INTEGER,
> ELNOK CHAR_70 COLLATE PXW_HUNDC,
> UGYINTEZO CHAR_70 COLLATE PXW_HUNDC,
> TEL CHAR_25 COLLATE PXW_HUNDC,
> IRSZ CHAR_6 COLLATE PXW_HUNDC,
> VAROS CHAR_70 COLLATE PXW_HUNDC,
> UTCA_HSZ CHAR_70 COLLATE PXW_HUNDC,
> BANK CHAR_40 COLLATE PXW_HUNDC,
> SZAMLASZAM CHAR_40 COLLATE PXW_HUNDC,
> MEGJEGYZES MEMO);
>
> ALTER TABLE TARSULAT_TORZS ADD PRIMARY KEY (TARSULAT_KOD);
>
> CREATE UNIQUE INDEX TARSULAT_TORZS_IDX1 ON TARSULAT_TORZS (NEV);
> -------------------------------------------------------------------
>
> I have the following query:
>
> SELECT
> BT.B_KOD,
> BT.P_KOD,
> BT.DATUM,
> BT.ROGZITVE,
> BT.OSSZEG,
> BT.EGYOSSZEGU,
> BT.MEGJEGYZES,
> PT.NEV,
> PT.P_KOD,
> PT.TARSULAT_KOD,
> TT.NEV,
> TT.TARSULAT_KOD
> FROM BEFIZETES_TORZS BT, PARTNER_TORZS PT, TARSULAT_TORZS TT
> WHERE BT.P_KOD=PT.P_KOD AND PT.TARSULAT_KOD=TT.TARSULAT_KOD
> ORDER BY BT.DATUM
>
> In this case FB 1.5 and 2.0 and 2.1 doesn't use the index defined on
> BEFIZETES_TORZS's DATUM field. If the TARSULAT_TORZS table is missing
> from the query all is OK, the index based on BEFIZETES_TORZS's DATUM
> field is used for the sorting.
>
> Here is the plan for the wrong case:
> PLAN SORT (JOIN (TT NATURAL,PT INDEX (PARTNER_TORZS_IDX2),BT INDEX
> (BEFIZETES_TORZS_IDX1)))
>
> And the good plan:
> PLAN JOIN (BT ORDER BEFIZETES_TORZS_IDX2,PT INDEX (RDB$PRIMARY2))
>
> What can I do for using the appropriate index in the mentioned
> situation? Do I have to provide my own plan?
>
> TIA:Alex :-)