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
