On Wed, Jun 22, 2011 at 9:25 PM, Rense Corten <rcor...@gmail.com> wrote:
> > CREATE TABLE table2 AS SELECT n1,n2 FROM (SELECT n1, n2 FROM table1 > UNION SELECT n2 AS n1, n1 AS n2 FROM table1) WHERE(n1<n2) GROUP BY > n1,n2; > > This has the desired result on a small example, but when I try this on > my actual table which has about 800 million rows, the query never > seems to complete. It has been running for a couple of days now, and > it doesn't seem sqlite is still doing anything (cpu usage dropped to > almost zero)... > Rense, looks like an interesting task. What are the ranges of n1 and n2? We can take sqlite logic into account, but regardless of the one, if the range is small comaring to the row count, the result set will be small, so there's always possible to do full scan maintaining the result as a small table (or memory array). I suppose sqlite sometimes goes this way after the some guessing. But if the range is large (so also a large result set is expected), there's no way other then prior sorting of both sub-tables (or probably by creating two indexes). Looking at how your query executed against my test data and seeing I/i read and write statistics, sqlite probably does these temporary tables creation. I noticed that fewer reads/writes will be with the following variant SELECT * FROM (SELECT n1, n2 FROM table1 Where n1 < n2) UNION SELECT * FROM (SELECT n2, n1 FROM table1 Where n2 < n1) since this one decreases the sizes of the tables that should be ordered. Max Vlasov _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
