Hi Norbert!

It isn't a matter of there being data in the tables, but a matter of
which data in the tables. The optimizer simply thinks that with the
current content of the tables, then plan B is better than plan A.
Change the contents of the tables (e.g. add records to the table with
the least records) and your plan may change. If you want to prevent
the optimizer from choosing the plan it thinks is the best, do this
change to your query: "ON T100.T100_ID = T110.T100_ID + 0"

Though I'm not certain which plan is the best in your case.

Set

--- In firebird-support@yahoogroups.com, "Norbert Nemeth" wrote:
> Hi,
>
> I have 2 tables (relation: T100 and T110 ==> 1:n)
>
> CREATE TABLE T100 (
> T100_ID INTEGER NOT NULL,
> T100_COL1 VARCHAR(40),
> T100_COL2 SMALLINT,
> CONSTRAINT PK_T100 PRIMARY KEY (T100_ID)
> );
>
> CREATE TABLE T110 (
> T110_ID INTEGER NOT NULL,
> T100_ID INTEGER NOT NULL,
> T110_NR INTEGER NOT NULL,
> CONSTRAINT PK_T110 PRIMARY KEY (T110_ID)
> );
>
> CREATE UNIQUE DESC INDEX IDX_T110 ON T110 (T100_ID,T110_NR);
>
> ALTER TABLE T110
> ADD CONSTRAINT FK_T110_REF_T100 FOREIGN KEY (T100_ID)
> REFERENCES T100 (T100_ID);
>
>
> Select:
> SELECT T100.T100_COL1
> , T100.T100_COL2
> , T110.T110_NR
> FROM T110
> JOIN T100
> ON T100.T100_ID = T110.T100_ID
> ORDER BY T110.T100_ID DESC, T110.T110_NR DESC
>
> 1. If the tables are empty, than:
> PLAN JOIN (T110 ORDER IDX_T110,T100 INDEX (PK_T100))
>
> 2. If it contains records, than:
> PLAN SORT (JOIN (T100 NATURAL,T110 INDEX (FK_T110_REF_T100)))
> ^^^^^^^^^^^^^^ why?
>
> Why does not the server (FB 1.5.2.4731) use the indexes
> according to the first plan (in case of empty tables)?
>
> Regards,
> Norbert