Hi again, Any takes on this problem?
I've also tried adding an index to the WS table before running the SELECT DISTINCT query but that has the same problem: The statement keeps running and the database file keeps growing (no temp files). Further, I get the impression that the problem has to do with the actual data in the table. Because I've been able to execute the given SELECT DISTINCT query successfully with far more, albeit shorter, values in the table. But even in this case the values are not excessively long: SELECT MIN(LENGTH(VAL)), AVG(LENGTH(VAL)), MAX(LENGTH(VAL)) FROM WS; MIN(LENGTH(VAL)) AVG(LENGTH(VAL)) MAX(LENGTH(VAL)) 3 31 672 Kind regards, Knut Wannheden On Mon, Dec 12, 2011 at 15:59, Knut Wannheden <[email protected]> wrote: > 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.
