Hi all,
I have a very simple table defined as:
CREATE TABLE WS(VAL VARCHAR);
without any indexes on it containing around 1 million rows:
SELECT COUNT(VAL), COUNT(DISTINCT VAL)
FROM WS;
COUNT(VAL) COUNT(DISTINCT VAL)
944917 885342
I would now like to create a new table containing all distinct values
only. Trying with various variations of statements like:
CREATE TABLE WS2(VAL VARCHAR) AS
SELECT DISTINCT(VAL)
FROM WS;
seems to take forever. Actually I've never seen the statement complete
while the database file keeps growing to multiple GBs in size.
I haven't specified any properties in the connection URL.
With jps I can see that H2 is in a very deeply nested call:
"H2 Console thread" prio=6 tid=0x000000000c5f5800 nid=0x5628 runnable
[0x000000000ffc9000]
java.lang.Thread.State: RUNNABLE
at org.h2.value.Value.compareTypeSave(Value.java:867)
at org.h2.table.Table.compareTypeSave(Table.java:1037)
at org.h2.index.BaseIndex.compareValues(BaseIndex.java:244)
at org.h2.index.BaseIndex.compareRows(BaseIndex.java:176)
at org.h2.index.PageBtree.find(PageBtree.java:117)
at org.h2.index.PageBtreeNode.addRowTry(PageBtreeNode.java:198)
at org.h2.index.PageBtreeNode.addRowTry(PageBtreeNode.java:200)
at org.h2.index.PageBtreeNode.addRowTry(PageBtreeNode.java:200)
at org.h2.index.PageBtreeNode.addRowTry(PageBtreeNode.java:200)
at org.h2.index.PageBtreeNode.addRowTry(PageBtreeNode.java:200)
at org.h2.index.PageBtreeNode.addRowTry(PageBtreeNode.java:200)
at org.h2.index.PageBtreeNode.addRowTry(PageBtreeNode.java:200)
...
... continues for quite some frames
...
at org.h2.index.PageBtreeNode.addRowTry(PageBtreeNode.java:200)
at org.h2.index.PageBtreeNode.addRowTry(PageBtreeNode.java:200)
at org.h2.index.PageBtreeNode.addRowTry(PageBtreeNode.java:200)
at org.h2.index.PageBtreeIndex.addRow(PageBtreeIndex.java:105)
at org.h2.index.PageBtreeIndex.add(PageBtreeIndex.java:96)
at org.h2.table.RegularTable.addRow(RegularTable.java:130)
at org.h2.result.ResultTempTable.addRow(ResultTempTable.java:106)
at org.h2.result.LocalResult.addRow(LocalResult.java:262)
at org.h2.command.dml.Select.queryFlat(Select.java:523)
at org.h2.command.dml.Select.queryWithoutCache(Select.java:617)
at org.h2.command.dml.Query.query(Query.java:298)
at org.h2.command.dml.Insert.insertRows(Insert.java:132)
at org.h2.command.dml.Insert.update(Insert.java:84)
at org.h2.command.ddl.CreateTable.update(CreateTable.java:176)
at org.h2.command.CommandContainer.update(CommandContainer.java:71)
at org.h2.command.Command.executeUpdate(Command.java:212)
- locked <0x00000001854ea000> (a org.h2.engine.Database)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:177)
- locked <0x000000018aa1cf10> (a org.h2.engine.Session)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:152)
at org.h2.server.web.WebApp.getResult(WebApp.java:1311)
at org.h2.server.web.WebApp.query(WebApp.java:1001)
at org.h2.server.web.WebApp$1.next(WebApp.java:964)
at org.h2.server.web.WebApp$1.next(WebApp.java:967)
at org.h2.server.web.WebThread.process(WebThread.java:166)
at org.h2.server.web.WebThread.run(WebThread.java:93)
at java.lang.Thread.run(Unknown Source)
Any suggestions?
Thanks,
Knut Wannheden
--
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.
