On 04/ 6/10 08:17 AM, Fedd Kraft wrote: > Sorry for a followup.. > > So there are two problems: my problem is the wrong query plan, and the derby > problem is (not very efficient) hex string creation when scanning an index > to find a char for bit data value, with unordered nulls semantics. > > The latter is worth posting to Jira (I'd do that if I'd be able to reproduce > it outside my project);
Thanks for posting the extra info. I've logged this in JIRA as DERBY-4608, and I think there is a simple fix for it (use isNull() instead of getString() to check if the value is null when comparing the values). > but me personally wouldn't face that problem at all > if derby chose right index. When the right index is chosen there are no > phrases like "qualifiers:...Ordered nulls: false", and when debugging, the > execution skips that strange 'toString' place. > You can tell the optimizer to always use a certain index with an optimizer override, like this: select kee from res --derby-properties index=name_of_index where dnk=? and qid=? See these sections in the tuning guide for more details: http://db.apache.org/derby/docs/10.5/tuning/ctundepthoptover.html http://db.apache.org/derby/docs/10.5/tuning/ctunoptimzoverride.html > Thank you and please help me with the plan. Maybe I could call "select > count(*) from res" periodically to update the stats? Does that really update > the stats in Derby and some other systems? > It will update the estimated row count for the table, but it won't update the index cardinality statistics that are used by the optimizer to find which index to choose. This section in the tuning guide lists when the index cardinality statistics are automatically updated: http://db.apache.org/derby/docs/10.5/tuning/ctunstats57373.html Additionally, there is a system procedure that can be used to update the statistics manually: http://db.apache.org/derby/docs/10.5/ref/rrefupdatestatsproc.html Hope this helps, -- Knut Anders
