Re: Optimizer hints?

[Rajesh Kartha]

I think (3) would be really useful.  The tool can ingest hints in the 
form of 'properties' or XML and let Derby know
accordingly what method/join to use for the execution of a particular SQL.

-Rajesh

Satheesh Bandaram wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I have been thinking about adding optimizer hints to Derby. Though Derby
optimizer does perform a reasonable job, it may be useful to have
optimizer hints for cases 1) when updated index statistics is not
available, or even incorrect 2) Rare cases, like this one?, when the
optimizer doesn't do the job right 3) when applications issue queries
not designed for Derby (like very large number of tables).
Derby optimizer is primarily tasked to handle 1) access method (whether
to use table scan or index) 2) join order and 3) join strategy (nested
loop or HashJoin) A complete optimizer hints (should they actually be
called optimizer overrides?) should be able to provide means of
specifying all these. There are several ways to do this, including
  1. Enhance the SQL to recognize additional keywords, like properties.
These properties could specify optimizer hints. That is what Cloudscape
had before, but this causes non-portable SQL to be written. Attempts to
run these statements against any other SQL server could cause syntax
errors. Not an ideal solution, according to me.
  2. Provide optimizer hints as SQL comments. These comments are
recognized only by Derby parser and other SQL engines would simply
ignore these. There are some limitations in Derby parser currently to
implement this option.
  3. Provide an offline tool which registers hints for SQL statements.
When those SQL statements are executed, Derby could look up previously
registered hints for those statements and automatically apply them.
Advantage of this scheme is that applications don't need to be modified
to add hints and they can easily be removed when not needed. How exactly
to register the hints could be worked out. I personally prefer this
approach.
Any comments?
Satheesh
Gerald Khin (JIRA) wrote:
 

HashJoinStrategy leads to java.lang.OutOfMemoryError
----------------------------------------------------
       Key: DERBY-106
       URL: http://nagoya.apache.org/jira/browse/DERBY-106
   Project: Derby
      Type: Bug
  Reporter: Gerald Khin
My application is running out of memory: I encounterd a
   

java.lang.OutOfMemoryError. I used -Xmx256M. Unfortunatley, I cannot
spend an arbitrary amount of JVM memory.
 

Then, I commented out the line in class OptimizerFactoryImpl which was
   

adding the HashJoinStrategy to the set of Join strategies:
 

      if (joinStrategySet == null)
      {
//            JoinStrategy[] jss = new JoinStrategy[2];
          JoinStrategy[] jss = new JoinStrategy[1];
          jss[0] = new NestedLoopJoinStrategy();
//            jss[1] = new HashJoinStrategy();
          joinStrategySet = jss;
      }
And with these changes the OutOfMemoryError has gone away! And it works
   

even with -Xmx128M!!!
 

So I guess that there is a major memory issue with this HashJoin
   

strategy implementation.
 

If it turns out to be too complicated to make the memory consumption
   

more predicatble or even bounded to some configurable limit, then I need
at least as a workaround a way to turn off the HashJoin strategy
completely: I did it by patching and building my own derby.jar, but if
there would be an official solution with some kind of switch like a
system property, it would be great!
 

   

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFBw2ClENVNIY6DZ7ERAtofAJ0Sgt4OnJPawnVlF22hI7+y7wZyvwCeKjOw
aD0AJPCaifS2ZEq50cU5TWk=
=Bq8A
-----END PGP SIGNATURE-----