Hi Thomas - > > how to work out for a given table size how much cache > > must be allocated to keep its primary key index in memory? > > There is no simple answer for that question. You have to test it yourself.
Yes, I knew that there is no way of getting around this but I was hoping for some rough figures to be be able to postpone it. > But, again, if you want to keep the whole index in memory you could > use "memory tables":http://www.h2database.com/html/grammar.html#create_table I'm not quite able to see how this would work. If I understand your suggestion correctly, it's not possible to create an INDEX as an in- memory index directly, but if you create an index on a table that's defined as a in-memory table the index will also reside in memory. But the problem, it seems to me, is that there isn't any way to get the rows of your memory-resident table to refer to the rows in your persitent table directly, that is in a way that won't require either scanning or the use of the original persistent index. The best one could do would be to have the in-memory table rows reference the primary keys of your persisent table as foreign keys. But to follow a reference from a row in the in-memeory table to a row in the persistent table would still require looking up the actual location of the persistent record by scanning or using the persistent index. Though it's very possible that I'm missing something very basic. I only just stumbled over the documentation that says that hash indexes stay in memory. This sounds ideal for our situation so will try this out. Best regards, Paul -- 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.
