Hello,
when we create foreign key an index is created, but this index can have a very
weak selectivity and then the plan is not very good
for example :
in this example the field STATUS has only 3 différents values

CREATE TABLE MACHINS (
ID_MACHINS INTEGER NOT NULL,
TEXTE_MACHINS VARCHAR(255),
STATUS INTEGER NOT NULL
);

CREATE TABLE STATUS (
ID_STATUS INTEGER NOT NULL,
TEXTE_STATUS VARCHAR(255)
);

ALTER TABLE MACHINS ADD CONSTRAINT PK_MACHINS PRIMARY KEY (ID_MACHINS)
USING DESCENDING INDEX IDX_PK_MACHINS;
ALTER TABLE STATUS ADD CONSTRAINT PK_STATUS PRIMARY KEY (ID_STATUS)
USING DESCENDING INDEX IDX_PK_STATUS;

ALTER TABLE MACHINS ADD CONSTRAINT FK_MACHINS_STATUS FOREIGN KEY (STATUS)
REFERENCES STATUS (ID_STATUS) ON DELETE NO ACTION ON UPDATE CASCADE
USING DESCENDING INDEX IDX_FK_STATUS;

I know that if I make :
SELECT * FROM MACHINS WHERE STATUS=1;
the plan is :
PLAN (MACHINS INDEX (IDX_FK_STATUS))

and when I make :
SELECT * FROM MACHINS WHERE STATUS+0=1;
the plan is (and it is better) :
PLAN (MACHINS NATURAL)

Is there another way ?


--
Philippe Makowski

Firebird serveur SQL open-source en français
http://firebird-fr.eu.org

Ma clé PGP : http://makowski.net/pgpkey.html