Sorry, of course it's not "classes" but objects :-) On May 11, 1:18 pm, phillnet <[email protected]> wrote: > Hi, first of all thanks for solving my previously reported issues. > > Now, here's somtehing new: > > Step by step: > ---------------------------------------------------------------------------------------- > > I open two connections (connection, movieDB) to the same database in > two different objects. > > I crate two cached temporary tables: > > (1) connection.createStatement().execute("CREATE CACHED GLOBAL > TEMPORARY TABLE RatingsList(hash BINARY(16), title varchar(256), year > YEAR, rating varchar(4), type varchar(4))"); > (2) connection.createStatement().execute("CREATE CACHED GLOBAL > TEMPORARY TABLE GenresList(hash BINARY(16), genre TINYINT)"); > > I fill both tables (200.000 and 900.000 entries). > > I create indexes on both tables: > > (1) connection.createStatement().execute("CREATE UNIQUE INDEX > idx_RatingsList_hash ON RatingsList(hash)"); > (2) connection.createStatement().execute("CREATE INDEX > idx_GenresList_hash ON GenresList(hash)"); > > I create a view on both tables joined: > > movieDB.createStatement().execute("CREATE VIEW JoinedRatingsList > (hash, title, year, rating, type) AS SELECT DISTINCT RatingsList.hash, > RatingsList.title, RatingsList.year, RatingsList.rating, > RatingsList.type FROM RatingsList INNER JOIN GenresList ON > RatingsList.hash = GenresList.hash"); > > I insert parts of the columns of the view to the destination table > (titles): > > movieDB.createStatement().execute("CREATE TABLE titles(id INT > AUTO_INCREMENT PRIMARY KEY, title varchar_ignorecase(255), hash > BINARY(16))"); > > movieDB.createStatement().execute("INSERT INTO titles(hash, title, id) > SELECT hash, title, null FROM JoinedRatingsList"); > > I create an index in the destination table: > > movieDB.createStatement().execute("CREATE UNIQUE INDEX idx_hash ON > titles(hash)"); > > I close the first connection (connection variable). > > I close the second connection (movieDB variable). > ---------------------------------------------------------------------------------------- > > Now the problem is: If there is only the filled title table in the > database, it is about 13 MB large. If i create an index on it as > described above it is about 96 MB large. > > I can "solve" the problem, if I drop both temporary tables before I > create an index: > > ---------------------------------------------------------------------------------------- > [...] > movieDB.createStatement().execute("INSERT INTO titles(hash, title, id) > SELECT hash, title, null FROM JoinedRatingsList"); > > > I drop both temporary tables: > > > connection.createStatement().execute("DROP TABLE "+tableName); x2 > > I create an index in the destination table: > > movieDB.createStatement().execute("CREATE UNIQUE INDEX idx_hash ON > titles(hash)"); > [...] > ---------------------------------------------------------------------------------------- > > Now the database storing the filled titles table plus one index is 20 > MB large, and this value is somehow an expected value. > > So why is this? I !guess! if I create an index on the newly filled > table, the database engine somehow "forgets" to clean up data > coresponding to the temporary tables. Could this be? > > Thanks in advance. > > Regards, > Philipp > > -- > 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 > athttp://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.
