We have a simple query using LIKE but is very slow. We need to tell the
optimizer which index to use but the override DERBY-PROPERTIES statement
returns an error. here is the SQL.
SELECT
esq.attachment.guid
FROM
esq.attachment
--DERBY-PROPERTIES index = ATTACHMENT_LINKNAMES_INDEX
where
(
esq.attachment.file_name LIKE ‘%drisco%'
OR esq.attachment.file_index_name LIKE ‘%drisco%'
)
AND
(
esq.attachment.link_guid IS NOT NULL
AND
esq.attachment.link_table='mat'
AND
esq.attachment.link_guid IN (
SELECT esq.matter.guid
FROM esq.matter
WHERE
esq.matter.guid=esq.attachment.link_guid
AND (
( esq.matter.grp_guid=0 OR esq.matter.grp_guid
IS NULL ) OR ( esq.matter.grp_guid>0 AND esq.matter.grp_guid IN ( 2,1,6,4,3 ) )
)
)
) ;
Sincerely,
Frank Rivera
LogicBit Software - Research Triangle Park, North Carolina
