Submitted by: @pmakowski

Two tables :
CREATE TABLE TABLE1
(
ID INTEGER NOT NULL,
CHAMP1 VARCHAR( 32) COLLATE ISO8859_1,
TABLE2_ID INTEGER NOT NULL,
REF_REMPLACEMENT VARCHAR( 10000) COLLATE ISO8859_1,
CONSTRAINT PK_TABLE1 PRIMARY KEY (ID)
);
CREATE TABLE TABLE2
(
ID INTEGER NOT NULL,
CHAMP2 VARCHAR( 32) COLLATE ISO8859_1,
CONSTRAINT PK_TABLE2 PRIMARY KEY (ID)
);
CREATE ASC INDEX IDX_TABLE1_1 ON TABLE1 (CHAMP1);
CREATE ASC INDEX IDX_TABLE2_1 ON TABLE2 (CHAMP2);
ALTER TABLE TABLE1 ADD CONSTRAINT FK_TABLE1_1
FOREIGN KEY (TABLE2_ID) REFERENCES TABLE2
(ID)
ON DELETE NO ACTION
ON UPDATE CASCADE
;

The SP to fill tables :

CREATE PROCEDURE SP_GEN_DATA AS
declare i integer;
declare j integer;
begin
i=0;
j=0;
while(i<=15000)do
begin
while(j<=4) do
begin
j=j+1;
INSERT INTO table2 VALUES (:j,'TBL'||:j);

end
INSERT INTO Table1 VALUES (:i,'VAL'||:i,:j,NULL);

i=i+1;
end
end

and here the queries :

the first that is slow :

SELECT Table1.Champ1,Table1.REF_REMPLACEMENT,Table2.Champ2
FROM Table1 INNER JOIN Table2 ON Table1.Table2_ID=http://Table2.ID
ORDER BY Table1.Champ1

and two others that have good speed :

SELECT Table1.Champ1,Table2.Champ2
FROM Table1 INNER JOIN Table2 ON Table1.Table2_ID=http://Table2.ID
ORDER BY Table1.Champ1

WITH A (Champ1,Champ2,ID) AS
(SELECT Table1.Champ1, Table2.Champ2, http://Table1.ID
FROM Table1 INNER JOIN Table2 ON Table1.Table2_ID=http://Table2.ID
ORDER BY Table1.Champ1)
SELECT A.Champ1, Table1.REF_REMPLACEMENT,A.champ2
FROM A JOIN Table1 ON http://Table1.ID=A.ID

funny to see that the latest make twice more indexed reads on Table1 but is really faster that the initial one

Commits: 8e692d2