I have just changed a query that is part of a stored procedure. The
query used an "=" predicate and I had to change it to the "in" predicate.

This is the "old" query:

update VIRTUAL_FILES set VIRTUAL_PATH_FILE_ID = ?
where FILE_ID = (select VIRTUAL_FILES.FILE_ID
from VIRTUAL_FILES inner join FILES on
VIRTUAL_FILES.PHYSICAL_FILE_ID = FILES.FILE_ID
where FILES.PATH_FILE_ID = ?);


and this is the "new" query:

update VIRTUAL_FILES set VIRTUAL_PATH_FILE_ID = ?
where FILE_ID IN (select VIRTUAL_FILES.FILE_ID
from VIRTUAL_FILES inner join FILES on
VIRTUAL_FILES.PHYSICAL_FILE_ID = FILES.FILE_ID
where FILES.PATH_FILE_ID = ?);

I had to change this because I thought that the query would need to
update only a single row, but now I discovered that the rows might be a
few (but really few).
The problem is that the old query was fast, while the new one is deadly
slow.
The query references two tables, but the database contains more of them.
Here is the DDL for the two tables:

CREATE TABLE FILES(
FILE_ID Numeric(18,0) NOT NULL,
FILE_NAME Varchar(500) CHARACTER SET ISO8859_1,
FILE_SIZE Numeric(18,0),
FILE_EXT Varchar(50) CHARACTER SET ISO8859_1,
FILE_DATETIME Timestamp,
PATH_ID Numeric(18,0),
PATH_FILE_ID Numeric(18,0),
CONSTRAINT PK_FILES PRIMARY KEY (FILE_ID)
);
ALTER TABLE FILES ADD CONSTRAINT FK_FILES_PATHS
FOREIGN KEY (PATH_ID) REFERENCES PATHS (PATH_ID);
ALTER TABLE FILES ADD CONSTRAINT FK_FILES_PATHS2
FOREIGN KEY (PATH_FILE_ID) REFERENCES PATHS (PATH_ID);


CREATE TABLE VIRTUAL_FILES(
FILE_ID Numeric(18,0) NOT NULL,
VIRTUAL_PATH_ID Numeric(18,0) NOT NULL,
PHYSICAL_FILE_ID Numeric(18,0) NOT NULL,
VIRTUAL_PATH_FILE_ID Numeric(18,0),
CONSTRAINT PK_VIRTUAL_FILES PRIMARY KEY (FILE_ID)
);
ALTER TABLE VIRTUAL_FILES ADD CONSTRAINT FK_VIRTUAL_FILES_FILES
FOREIGN KEY (PHYSICAL_FILE_ID) REFERENCES FILES (FILE_ID);
ALTER TABLE VIRTUAL_FILES ADD CONSTRAINT FK_VIRTUAL_FILES_VIRTUAL_PATHS
FOREIGN KEY (VIRTUAL_PATH_ID) REFERENCES VIRTUAL_PATHS (PATH_ID);
ALTER TABLE VIRTUAL_FILES ADD CONSTRAINT FK_VIRTUAL_FILES_VIRTUAL_PATHS2
FOREIGN KEY (VIRTUAL_PATH_FILE_ID) REFERENCES VIRTUAL_PATHS (PATH_ID);


The plan for the old query is the following:

PLAN JOIN (FILES INDEX (FK_FILES_PATHS2), VIRTUAL_FILES INDEX
(FK_VIRTUAL_FILES_FILES))
PLAN (VIRTUAL_FILES INDEX (PK_VIRTUAL_FILES))

and the plan for the new query is the following:

PLAN JOIN (VIRTUAL_FILES INDEX (PK_VIRTUAL_FILES), FILES INDEX (PK_FILES))
PLAN (VIRTUAL_FILES NATURAL)


I suppose that the problem is the "natural" word that I see in the plan:
the optimizer chooses not to use any index.

I am using Firebird 2.01 SS on Windows XP.

Does anybody know how to make fast again the query with the "in"
predicate? I know that I can rewrite the query in a different way, but I
am also curious about the reason of this huge difference.

Thanks in advance.

Fulvio Senore