db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Derby internally converts char for bit data to hex string
Date Tue, 06 Apr 2010 09:30:13 GMT
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


Mime
View raw message