Hello guys, today I've stumbled upon problem with performance. I have such table:
CREATE TABLE DETAILS_DATA ( ID_MASTER INTEGER NOT NULL, ID_PARAM INTEGER NOT NULL, PARAM_VALUE VARCHAR(64) NOT NULL ); ALTER TABLE DETAILS_DATA ADD CONSTRAINT FK_DETAILS_DATA_ID_MASTER FOREIGN KEY (ID_MASTER) REFERENCES MASTER_DATA (ID); CREATE UNIQUE INDEX UNQ_DETAILS_DATA ON DETAILS_DATA (ID_MASTER,ID_PARAM); when performing this query: SELECT DD.PARAM_VALUE FROM DETAILS_DATA DD WHERE DD.ID_PARAM = 60 only the NATURAL plan is used. I thought that index UNQ_DETAILS_DATA will be used. I've tried to enforce a specific plan: SELECT DD.PARAM_VALUE FROM DETAILS_DATA DD WHERE DD.ID_PARAM = 60 PLAN (DD INDEX(UNQ_DETAILS_DATA)) But I've got message "index UNQ_DETAILS_DATA cannot be used in the specified plan". FInally I did silly thing and joined this table with master table: SELECT DD.PARAM_VALUE FROM DETAILS_DATA DD INNER JOIN MASTER_DATA MD ON DD.ID_MASTER = MD.ID WHERE DD.ID_PARAM = 60 And now index is used and the whole query is working 20 times faster. Do I really need to include MASTER_DATA table to be able to use UNQ_DETAILS_DATA index? Thank you.
