Hi,
In situations where an index could assist JOIN ON LIKE, the index is
ignored:
create table test1(a varchar primary key) as select '1%';
create table test2(b varchar primary key) as select '11';
analyze;
explain plan for select * from test1 join test2 on test2.b = test1.a;
-- good: test2 index
explain plan for select * from test1 join test2 on test2.b like '1%'
and test1.a = '1%'; -- good: test2 index
explain plan for select * from test1 join test2 on test2.b like
test1.a and test1.a = '1%'; -- bad: test2 tableScan even though
test1.a doesn't necessary start with a wildcard character
Of course it is easy to use BETWEEN in some scenarios:
update test1 set a='1';
explain plan for select * from test1 join test2 on test2.b like
(test1.a||'%'); -- bad: test2 tableScan
explain plan for select * from test1 join test2 on test2.b between
test1.a and test1.a||stringdecode('\uffff'); -- good: test2 index
Is it a bug that JOIN ON LIKE simply ignores indices, or is it a
configurable option?
H2 Version 1.3.164 (2012-02-03)
[Apologies if this is a repost...Google glitch on my last submission]
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
