Hi Frank,
I am unable to reproduce the problem you are seeing when I run the
following script using Derby 10.10.1.1:
connect 'jdbc:derby:memory:db;create=true';
create table attachment( guid int, file_name varchar( 10 ) );
create index ATTACHMENT_LINKNAMES_INDEX on attachment( guid, file_name );
SELECT
attachment.guid, attachment.file_name
FROM
attachment
--DERBY-PROPERTIES index = ATTACHMENT_LINKNAMES_INDEX
;
The error message you're seeing is raised by the parser. I have
extracted the relevant method from the 10.10.2.0 parser into the
attached zzz.java program. I am unable to reproduce the problem you're
seeing when I run that program on your text. Try setting the PROPS field
in that program equal to what follows "--DERBY-PROPERTIES" in your query
and see if you can tickle the program into failing.
Hope this helps,
-Rick
On 4/11/14 9:33 AM, Frank Rivera 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 the following error.
*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
Product Brochures http://houdiniesq.com/HoudiniEsq_2014BrochureProduct.pdf
Product Testimonialshttp://houdiniesq.com/HoudiniEsq_2014BrochureShow.pdf
Support 919.238.7024Corporate 408.213.8138Sales 888.366.2280
***HoudiniEsq ® Legal Practice Management Suite *http://HoudiniEsq.com
<http://houdiniesq.com/>
Feature Rich web-based Legal Practice Management for today's cutting
edge Law Firms
Any Browser, Any OS, Anytime, Anywhere, Any size organization, SaaS
or On-premise
Simple & Simplicity are not the same thing. *"Simple", as it turns
out, simply isn't good enough.*
-----------------------------------------------------------------------------------------------------------------
*NOTICE*: The sender does not accept liability for any errors
or omissions in the contents of this message which arise as
a result of e-mail transmission. This e-mail and any files
transmitted with it are confidential and are intended solely for
the use of the individual or entity to which they are addressed. It is
not an offer or acceptance, and it is not intended to
be all or part of an agreement. This communication may
contain material protected by the attorney-client privilege.
Original material created by the author may be protected by US
Copyright law, 17 U.S.C. § 101 et seq. This communication
constitutes an electronic communication within the meaning of the
Electronic Communications Privacy Act, 18 U.S.C.
§ 2510, and its disclosure is strictly limited to the recipient
intended by the sender of this message. If you are not the
intended recipient or the person responsible for delivering the e-mail
to the intended recipient, be advised that you have
received this e-mail in error and that any review, use, dissemination,
forwarding, printing, or copying of this e-mail is strictly
prohibited. If you have received this e-mail in error, please notify
the sender immediately by return e-mail and delete this email.
import java.util.StringTokenizer;
import org.apache.derby.iapi.util.StringUtil;
public class zzz
{
private static final String PROPS = " index =
ATTACHMENT_LINKNAMES_INDEX";
private static final String SINGLEQUOTES = "\'\'";
private static final String DOUBLEQUOTES = "\"\"";
public static void main( String... args ) throws Exception
{
StringTokenizer commaSeparatedProperties = new
StringTokenizer(PROPS,",");
while (commaSeparatedProperties.hasMoreTokens()) {
//Now verify that tokens delimited by ,s follow
propertyName=value pattern
String currentProperty =
commaSeparatedProperties.nextToken();
StringTokenizer equalOperatorSeparatedProperty = new
StringTokenizer(currentProperty,"=", true);
if (equalOperatorSeparatedProperty.countTokens() != 3)
{
System.out.println( "Could not find 3 tokens" );
System.exit( 1 );
}
else {
String key =
equalOperatorSeparatedProperty.nextToken().trim();
if
(!equalOperatorSeparatedProperty.nextToken().equals("="))
{
System.out.println( "Next token is not =" );
System.exit( 1 );
}
String value =
equalOperatorSeparatedProperty.nextToken().trim();
if ( value.length() > 64000 )
{
System.out.println( "Value too long" );
System.exit( 1 );
}
/* Trim off the leading and trailing ', and
compress all '' to ' */
if (value.startsWith("'") &&
value.endsWith("'"))
value =
StringUtil.compressQuotes(value.substring(1, value.length() - 1), SINGLEQUOTES);
/* Trim off the leading and trailing ", and
compress all "" to " */
else if (value.startsWith("\"") &&
value.endsWith("\""))
value =
StringUtil.compressQuotes(value.substring(1, value.length() - 1), DOUBLEQUOTES);
else
value = value.toUpperCase();
}
}
}
}
