Hi, I don't know what the problem could be. There is a difference between a primary key that is created when there are no records in a table, and a primary key that is created after inserting data. But I couldn't reproduce the problem you describe. My test case is:
drop table test; set cache_size 0; create table test(id int primary key, name varchar); insert into test select x, space(10000) from system_range(1, 5000); explain analyze select * from test where id between 10 and 20; alter table test drop primary key; alter table test add primary key(id); explain analyze select * from test where id between 10 and 20; Could you try to change the test case to show the problem you observed? Regards, Thomas On Fri, Sep 24, 2010 at 10:58 AM, Dario Simone <[email protected]> wrote: > I forgot to mention: > > I'm using version 1.2.139 > > On Fri, Sep 24, 2010 at 10:15 AM, Dario <[email protected]> wrote: >> Hi all, >> >> All in all I'm really happy with H2 and (almost) everything works as >> it should. >> >> While looking at a query, with bad performance I came upon strange >> behaviour, though: >> H2 didn't use the primary key index to do a join which had only the >> table's primary key in the on-clause (explain showed a scanCount for >> that join of 106757553). After dropping the Index and recreating the >> primary key, the index was used (the scanCount was down to 35003). >> >> The only difference in the index before and after the recreation is, >> that the one before had IS_GENERATED = true and the one after had >> IS_GENERATED = false. >> >> It seems as if the index was there and if I understand the explain >> syntax correctly the optimiser decided to use it, but then scanned the >> whole table anyways in spite of the existing Index. >> >> Has somebody come across such a behaviour before? >> >> -- >> 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. >> >> > > -- > 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. > > -- 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.
