Emmanouil Karvounis wrote: > In short, we have two tables that are already sorted on a combination of > two fields (which are their primary keys) and we want to union them and > apply group by on both the aforementioned fields, like so: > > select c1, c2, myagg(*) from ( > select * from tableA > union all > select * from tableB > ) > group by c1, c2; > > The sqlite query plan creates a temporary B-tree to hold all the records of > both tables to execute the group by. This incurs too much time and space > complexity
If the number of result rows is small compared to the number of table rows, and if myagg() works correctly when called on its own results, then you can reduce the size of the temporary B-tree by doing another GROUP BY in the subqueries: select c1, c2, myagg(prop) from ( select c1, c2, myagg(prop) as prop from tableA group by c1, c2 union all select c1, c2, myagg(prop) from tableB group by c2, c2 ) group by c1, c2; Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
