Are you sure you are looking at the correct override (in case you have
several)?
I tried the one below and it didn't give me any syntax error.
A similar sample below also works as expected:
$ java -jar ~/java/sb/sb0/jars/sane/derbyrun.jar ij foo.sh
ij version 10.11
ij> connect 'jdbc:derby:memory:wombat;create=true';
ij> create table t(i int);
0 rows inserted/updated/deleted
ij> create index my_index on t(i);
0 rows inserted/updated/deleted
ij> SELECT
i
FROM
app.t
--DERBY-PROPERTIES index = MY_INDEX
where
(
i > 0
)
;
I
-----------
0 rows selected
On 11. april 2014 18:16, Frank Rivera wrote:
BTW the error is this
Invalid syntax for optimizer overrides. The syntax should be --
DERBY-PROPERTIES propertyName = value [, propertyName = value]*
Sincerely,
Frank Rivera
LogicBit Software - Research Triangle Park, North Carolina
On Apr 11, 2014, at 12:15 PM, Frank Rivera
<[email protected] <mailto:[email protected]>> wrote:
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
