db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Derby hangs when joins are used specially for larger resultset
Date Thu, 01 Apr 2010 18:33:11 GMT
Hi Mamatha,

Derby can't use indexes for this query because there are no indexes on 
the expressions resulting from calling the UPPER function. There may be 
two solutions to this issue:

1) Wait for the 10.6 release. That release will introduce 
case-insensitive string comparisons. See 
https://issues.apache.org/jira/browse/DERBY-1748

2) Add generated columns to your tables today and rewrite your query.

Solution (2) would look something like this:

alter table tidlrrep
add column upper_source_type generated always as upper( source_type );
alter table tidlrrep
add column upper_base_language_term generated always as upper( 
base_language_term );

alter table tidlggls
add column upper_base_language_term generated always as upper( 
base_language_term );

create index ulrrep on tidlrrep( upper_source_type, 
upper_base_language_term );
create index ulggls on tidlggls( upper_base_language_term );

SELECT count (*) FROM TIDLRREP LRREP, TIDLGGLS LGGLS WHERE 
lrrep.upper_source_type = 'COPYBOOK ‘ AND 
lrrep.upper_base_language_term= lggls.upper_base_language_term;

Hope this helps,
-Rick

Mamatha Kodigehalli Venkatesh wrote:
>
> Hello,
>
> Derby just cannot process the below query through ij editor and it 
> just hangs, whereas in oracle it just takes 2 sec’s.
>
> Derby and Oracle are reflecting the same data.
>
> There are around 46 thousand records that oracle fetches using this 
> query.
>
> SELECT count (*) FROM TIDLRREP LRREP, TIDLGGLS LGGLS WHERE UPPER 
> (LRREP.SOURCE_TYPE) = 'COPYBOOK ‘ AND UPPER(LRREP.BASE_LANGUAGE_TERM) 
> = UPPER(LGGLS.BASE_LANGUAGE_TERM);
>
> Any feedback on this, Cost Based Optimizer forcing an index for column 
> LRREP.BASE_LANGUAGE_TERM is also not of any help.
>
> Please advise how to make the joins work smoother.
>
> Thanks
>
> Mamatha
>


Mime
View raw message