Try doing CREATE TABLE WS2 ... INSERT INTO WS2 .... DIRECT SELECT DISTINCT ....
You could also try doing CREATE TABLE WS2 ... PRIMARY KEY (VAL) MERGE INTO ... SELECT VAL FROM WS; Knut Wannheden 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.
