-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
I don't have specific queries, but I can give examples where the optimizer may not have the right information to pick a good plan:
1) queries involving tables with no indexes have no data distribution ~ maintained by the system, so any guess the optimizer makes may be ~ wrong for a dataset. 2) queries involving indexes basically use the shape of the tree to ~ estimate data distribution. The cost information for an estimate ~ of number of rows between key1 and key2, assumes uniform distribution ~ throughout the tree. 3) because of #2, multi-column indexes (key1, key2) don't provide data ~ distribution information on key2 - so again the optimizer just uses ~ a default distribution guess.
RPost wrote: | Do you have any specific examples of queries that could benefit from an | optimizer hint? | | Does anyone know of any documented instances where Derby has selected an | execution plan that is ineffective or suboptimal? Were these instances able | to be corrected by tuning the system using existing | properties? | | Oracle allows hints to be provided as SQL comments. | | Re providing an offline tool. Does this method imply that the statements | exist independent of any given running database instance. If so are you | proposing that a prepared statement be saved and made available for future | execution without preparing it again? | | ----- Original Message ----- | From: "Satheesh Bandaram" <[EMAIL PROTECTED]> | To: "Derby Development" <[email protected]> | Sent: Friday, December 17, 2004 2:41 PM | Subject: Optimizer hints? | | | | 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
iD8DBQFBx3EOEpeslyHqPs0RAk9lAKC9q5QeYyb/lgCnT5/9eyV/ygL4xACfUnfx v4P9ttZUNaejoXSs6d5tPCg= =2gpL -----END PGP SIGNATURE-----
