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 classes.
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 at
http://groups.google.com/group/h2-database?hl=en.
