Hi,
There is only one query plan per statement, not one query plan per row. I
believe this is not just the case for H2, but for all other databases as
well.
TEST2.B LIKE TEST1.A
It is not possible to use an index here because depending on the data
TEST1.A could start with a '%'
Regards,
Thomas
On Wednesday, March 7, 2012, JND wrote:
> 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]<javascript:;>
> .
> To unsubscribe from this group, send email to
> [email protected] <javascript:;>.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.
>
>
--
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.
