tries to create the table with a primary index:

CREATE TABLE DETAILS_DATA
(ID_MASTER INTEGER NOT NULL,
ID_PARAM INTEGER NOT NULL,
PARAM_VALUE VARCHAR(64) NOT NULL,
CONSTRAINT "PK_DETAILS_DATA" PRIMARY KEY ("ID_MASTER","ID_PARAM"));






-------Messaggio originale-------

Da: brucedickinson@... [firebird-support]
Data: 17/07/2015 16.14.07
A: firebird-support@yahoogroups.com
Oggetto: [firebird-support] Is it possible to use only "part" of the index?


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
p lan".


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.







[Non-text portions of this message have been removed]