Hi, > If i create an index on a temporary table, this index is created & > fully kept in memory.
Yes, indexes of temporary tables are kept fully in main memory. This is not documented yet, I will do that. > For example: I create a temporary table with ~230000 entries (which is > in plain text around 13MB). While filling the table, java used memory > stays around 10MB. When the table is filled, I create an index on one > column and the amount of memory java uses jumps to 70MB. But the real > problem is, this memory never becomes available again until the > application closes. It should become available when you drop the temporary table. It does according to my test case (only works in the H2 Console): drop all objects; @memory; create temporary table test(id int primary key, name varchar); create index idx_1 on test(name); insert into test select x, x || space(1000) from system_range(1, 10000); @memory; drop table test; @memory; > Why is the index like 6-7 times larger than the temporary table? Java needs quite a lot of memory. Is it an option for you to create a regular table instead of a temporary table? > And why is it completely stored in memory, discounting the value set > for CACHE_SIZE? My idea was that temporary tables are usually very small and need to be very fast. That's why indexes of temporary tables are kept in memory. I can change that if required, but that's how it is currently. I will add a feature request, but will not yet change that unless others also request to change it. It's not a breaking change, but may be unexpected for others if I change it. Regards, Thomas -- 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.
