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: [email protected] [firebird-support]
Data: 17/07/2015 16.14.07
A: [email protected]
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]
