We have a simple query using LIKE but it is very slowwwwwww.
We need to tell the optimizer which index to use but the override
DERBY-PROPERTIES statement returns the following error. The index exist. I
don’t think prepending the index name with the schema is required.
“unqualified” should work. In either case same error.
Derby 10.1.1
Invalid syntax for optimizer overrides. The syntax should be --
DERBY-PROPERTIES propertyName = value [, propertyName = value]*
Here is the SQL. The index does exist.
SELECT
esq.attachment.guid, esq.attachment.file_name
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
