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.