I have a table Person and two columns firstName, lastName. I have a
composite index on two columns (firstName, lastName) as well. My query is
SELECT * FROM Person WHERE firstName IN ("FirstName1", "FirstName2") AND
lastName="LastName1". H2 doesn't use the index. In my case Y IN(..) will
produce far less rows than X=?.
I decided to have a look on source code. I have found a method
org.h2.index.IndexCursor#prepare. There is such a piece of code in the
method:
if (isStart || isEnd) {
// an X=? condition will produce less rows than
// an X IN(..) condition
inColumn = null;
inList = null;
inResult = null;
}
I agree that X=? condition will produce less rows than X IN(..). But I
doesn't agree that X=? will produce less rows than Y IN (..). This
condition excludes IN values from Row.
I changed in debug mode isStart and isEnd values to false and H2 will start
to use my index.
1. How can I manage this situation?
2. Does H2 have statistics like Oracle?
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
