[h2] Re: Query Optimizer limitations

[Anil Dasari]

Hi Adam,

I mean query is very slow. Explain plan looks good and it is using index. I 
did not compare with other databases. 

Thanks.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[h2] Query Optimizer limitations

[Anil Dasari]

Hi,

I have created a simple join query between two tables and performance is 
very bad.

I read the documentation and understand that query optimizer have some 
limitations. i would like to go thorough them before actually posting the 
my query having the issues.

Could you please point me to the query optimizer limitations ? thanks.

Thanks.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[h2] Multiple IN Queries with OR condition

[Anil Dasari]

Hi,

I am using h2 1.4.191 version.

I have created Person table with indexes on id and name.

*select * from Person p where p.id in ('1', '3') OR name in ('name0', 
'name1') order by id  *is not using indexes. 

As IN query does not use index, i have written the above query -

*select * from (*
* select * from Person p join table(joinId varchar(10) = ?) i on p.id = 
i.joinId*
*  UNION*
*select * from Person p join table(name varchar(10) = ?) i on p.id = 
i.name*
*) order by id*

But above query is not using index at all. union is using index without 
outer query.

* select * from Person p join table(joinId varchar(10) = ?) i on p.id = 
i.joinId*
*  UNION*
*select * from Person p join table(name varchar(10) = ?) i on p.id = i.name*

Could you please share the work around to fix the issue ? thanks.

Thanks.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.