Subject Distinct Optimization
Author svanderclock
I have this SQL request whith the keyword Distinct Inside :

********

Select
distinct
PROPERTY.ID,
TRANSAC.ID
From
PROPERTY
JOIN PROPERTY_PUBLICATION ON
PROPERTY.ID=PROPERTY_PUBLICATION.ID_PROPERTY
JOIN TRANSAC ON PROPERTY.ID=TRANSAC.ID_PROPERTY
Where
((PROPERTY.ID_GEO_PLACE STARTING WITH 'FR-00241095')) and
(PROPERTY_PUBLICATION.ID_READER in ('zzzz','xxx'))

PLAN SORT (JOIN (PROPERTY INDEX (RDB$FOREIGN50),TRANSAC INDEX
(RDB$FOREIGN130),PROPERTY_PUBLICATION INDEX (RDB$FOREIGN76)))

order by TRANSAC.ADVERTISEMENT_WEIGHT DESC

******
with
RDB$FOREIGN50 = Property.ID_GEO_PLACE
RDB$FOREIGN130 = transac.id_property
RDB$FOREIGN76 = property_publication.id_property
RDB$PRIMARY64 = property.ID
TRANSAC_AD_WEIGHT_IDX = transac.advertisement_weight

******

It's Very long (Because lot of record are retourned)
So i try to use the query plan below :

********

PLAN SORT (JOIN (TRANSAC INDEX (TRANSAC_AD_WEIGHT_IDX), PROPERTY
INDEX (RDB$PRIMARY64), PROPERTY_PUBLICATION INDEX (RDB$FOREIGN76)))

******

but i receive always this error (index cannot be used in the
specified plan.index TRANSAC_AD_WEIGHT_IDX cannot be used in the
specified plan.) ....

Thanks you by advance for your help
st├ęphane