You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Mirit Naim <mi...@enigma.com> on 2004/10/11 21:48:37 UTC
Hints
Hi.
I have a question regarding hints in derby.
I'm trying to run a simple query such as:
select col from tab where othercol=1;
with hints:
select col from tab
PROPERTIES index=<index name>
where othercol=1;
no matter how I put it I get syntax error on "PROPERTIES".
I try to run this command using eclipse/db visualizer.
Does anyone have an idea?
--------------------------------------------------------------------------------------------------------------------------------------------------------------
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom
they are addressed. If you have received this email in error please notify the originator of the message.
Scanning of this message is performed by SurfControl E-mail Filter software in conjunction with virus detection software.
Re: Hints
Posted by Jeffrey Lichtman <sw...@rcn.com>.
>What is forcing you to use hints?
>Query planner should use that index automatically.
>It it does not, it did a poor job. It would be good to know why and fix it.
>Having SQL database with declarative query syntax without need to worry
>about how it will be executed is very important for usability.
I have been working with databases and optimizers for about twenty years. I
have yet to see an optimizer that gets everything right every time.
Optimizers rely on cost estimates and modeling that cannot always be
accurate. Sometimes a poor query plan is caused by a bug, but sometimes
it's caused by something the implementation didn't anticipate (or couldn't
reasonably be expected to anticipate). Optimizer hints allow the user to do
something in the inevitable cases where the query optimizer doesn't choose
an adequate plan, for whatever reason. Without hints, the user is stuck
until someone changes the optimizer - a task that is often very difficult.
- Jeff Lichtman
swazoo@rcn.com
Check out Swazoo Koolak's Web Jukebox at
http://swazoo.com/
Re: Hints
Posted by Jan Hlavatý <hl...@code.cz>.
>>>Given that optimizer hints are useful (even necessary), how could one
>>>support them in a "standard" way? I suppose they could be hidden in
>>>comments, although building the support for this isn't simple
What is forcing you to use hints?
Query planner should use that index automatically.
It it does not, it did a poor job. It would be good to know why and fix it.
Having SQL database with declarative query syntax without need to worry
about how it will be executed is very important for usability.
Re: Hints
Posted by Daniel John Debrunner <dj...@debrunners.com>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Jeffrey Lichtman wrote:
>
>> The PROPERTIES keyword and optimizer hints have been removed
>> from Cloudscape before the code was contributed to Derby.
>> I'm not sure where you've found the PROPERTIES keyword, but
>> as far as I can tell it is no longer part of the manual.
>>
>> The reason it was removed is because the syntax is non-standard SQL.
>
>
> Removing optimizer hints (and other hints) from the language because
> they are non-standard is pretty ridiculous, in my opinion. Hints are
> almost inevitably non-standard. The SQL standard doesn't even recognize
> the concept of optimization, much less optimizer hints.
>
> Given that optimizer hints are useful (even necessary), how could one
> support them in a "standard" way? I suppose they could be hidden in
> comments, although building the support for this isn't simple
Another potential way to support hints is to do it in configuration,
outside of the application. Basically something that mapped from the
query string to a set of hints. This would then allow hints to be
applied to an existing application without any code changes, even to a
compiled application. To me, this seems a far more flexible approach
than requiring re-writes of the application.
An XML form might be the easiest, something like
<query>
select a, b from s, t where c = d
<hints>
<table> s <index> sx </index> </table>
<table> t <index> tx </index> </table>
</hints>
</query>
Though, relying on XML for Derby will increase its footprint.
Maybe there is a way to do something similar in the standard java
properties format, or a derby format just for this issue.
Dan.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFBcUBiIv0S4qsbfuQRAjYvAKDWrL9eUJZQnoaQN76rmlMrYGyLSgCdFHfZ
1rCMpvqMsCP6OIddf948xgA=
=98JC
-----END PGP SIGNATURE-----
Re: Hints
Posted by Jeffrey Lichtman <sw...@rcn.com>.
>The PROPERTIES keyword and optimizer hints have been removed
>from Cloudscape before the code was contributed to Derby.
>I'm not sure where you've found the PROPERTIES keyword, but
>as far as I can tell it is no longer part of the manual.
>
>The reason it was removed is because the syntax is non-standard SQL.
Removing optimizer hints (and other hints) from the language because they
are non-standard is pretty ridiculous, in my opinion. Hints are almost
inevitably non-standard. The SQL standard doesn't even recognize the
concept of optimization, much less optimizer hints.
Given that optimizer hints are useful (even necessary), how could one
support them in a "standard" way? I suppose they could be hidden in
comments, although building the support for this isn't simple (the lexer
strips out comments, so for the hints to make it into the query tree would
require some co-ordination between the lexer and the rest of the parser).
This still wouldn't be standard, but it would hide the non-standard syntax
so the query would still work with other database systems.
SQL comments in Derby are delimited by "--" and end-of-line, which would
force the user to put line breaks in their queries if they wished to use
hints. That is, the following:
select a, b from s, t where c = d
would have to change to:
select a, b
from s -- properties index=sx
,t -- properties index=tx
where c = d
I suspect supporting syntax in comments would involve a lot of work and
tricky coding. The Derby lexer rule for comments is as follows (from
sqlgrammar.jj):
SKIP :
{ /* comments */
<SINGLE_LINE_SQLCOMMENT: "--" (~["\n","\r"])* ("\n"|"\r"|"\r\n")>
}
This tells the lexer to ignore anything that starts with "--" up until the
end of the line. The skipped-over characters are not part of any token, and
are not accessible to the grammar. It might be possible to define two types
of comment, one starting with "-- properties" and the other starting with
"--" followed by anything other than "properties", and to make "--
properties" a regular token. The problem then remains how to recognize
end-of-line from within the grammar (note that a different rule tells the
lexer to ignore whitespace).
I remember that JavaCC has some sort of feature that allows the grammar to
set a state which then affects the behavior of the lexer. It might be
necessary to use this feature to implement commented hints.
- Jeff Lichtman
swazoo@rcn.com
Check out Swazoo Koolak's Web Jukebox at
http://swazoo.com/
Re: Hints
Posted by Jonas S Karlsson <js...@yesco.org>.
Mirit Naim wrote:
>I have a question regarding hints in derby.
>I'm trying to run a simple query such as:
>select col from tab where othercol=1;
>with hints:
>
>select col from tab
>PROPERTIES index=<index name>
>where othercol=1;
>
>no matter how I put it I get syntax error on "PROPERTIES".
The PROPERTIES keyword and optimizer hints have been removed
from Cloudscape before the code was contributed to Derby.
I'm not sure where you've found the PROPERTIES keyword, but
as far as I can tell it is no longer part of the manual.
The reason it was removed is because the syntax is non-standard SQL.
Any suggestion of any standard way to allow such properties to be
given to a query could be of interest.
Do you have a specific query that is causing problems?
I'm looking into an issue where the optimization time is really high
and will be investigating the operations of the optimizer...
/Jonas