Hi Set,

Thank you for your prompt reply.

I have changed my query as you have suggested to me
("ON T100.T100_ID = T110.T100_ID + 0") and it getting faster.

Is the "+ 0" an undocumented future? How can I get more information
about the method of the optimalisation?

Regards,
Norbert

> 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