optimizer override [was: Re: When to shut down a database]

["Dag H. Wanvik" <da...@oracle.com>]

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 
> <frank.rivera@HOUDINIESQ.COM <ma...@HOUDINIESQ.COM>> 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
>>
>>
>