db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rajesh Kartha <kar...@Source-Zone.Org>
Subject Re: Optimizer hints?
Date Sat, 18 Dec 2004 00:13:24 GMT
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-----
>
>  
>


Mime
View raw message