Hello, OJB uses prepared statements to run select statements. Is there a way to convince OJB to use dynamic statements instead?
We are about to refactor a running system and want to use OJB instead of coded sql-statements. The problem is, that a prepared statement (=static statement) seems to limit the possibilities of the database query optimizer. We have quite big tables where each query *must* use an appropriate index. A prepared statement is compiled and optimized without any concrete information about the values of the query. As a result, the database (DB2 in our case) generates different access-pathes what is killing our application (query execution takes 30-40 minutes instead of around 0.4 secs). The reason seems to be a bad value-distribution: We have around 2,5 million values each appearing once, and one value appearing 11 million times. If a query uses that one value in a where-condition, the index on that column doesn't help at all (ends in a almost full table scan). The optimizer seems to recognize this in a dynamic statement and chooses another index, but naturally not in a static one. We cannot change the indizes. Runstats are uptodate and ran with option 'with distribution and detailled indexes all'. The only way we currently see is to use dynamic statements. Any ideas are very welcome. Norbert. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
