Derby hangs when joins are used specially for larger resultset

[Mamatha Kodigehalli Venkatesh <Ma...@ness.com>]

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

 

Re: Derby hangs when joins are used specially for larger resultset

[Rick Hillegas <ri...@oracle.com>]

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
>