On Mon, Oct 29, 2012 at 8:08 PM, Richard Hipp <d...@sqlite.org> wrote: > The reason for using a temp table for UNION ALL in a subquery is because > that is the path of least resistance. [...]
For what it's worth, UNION ALL is generally understood to be a major optimization over UNION because no temp table should be required to implement UNION ALL;. Also, IIUC, INTERSECT and EXCEPT could also be run without temp tables by converting them to slightly different queries: SELECT ... FROM source1 ... WHERE EXISTS (SELECT ... FROM source2 ...). And UNION ALL queries could be re-written into a UNION where the second query is an EXCEPT query (all the rows in the second that are not in the first). INTERSECT and EXCEPT are not used very frequently, but UNION ALL is used quite frequently, and probably much more so than UNION: UNION ALL is the logical operator for building VIEWs to implement a SQL equivalent of virtual classes, with each virtual class implemented as the UNION ALL of several queries. It would be quite worthwhile to have UNION ALL not use a temp table. Incidentally, there are a lot of possible optimizations where a query could be re-written differently where the optimizer could be external to SQLite, using a parser that objectifies SQL, an analyzer, and a re-writer that outputs SQL to be parsed by SQLite. Optimizations like GCSE, OR -> UNION, INTERSET/EXECPT -> SELECT ... WHERE EXISTS / NOT EXISTS, and others. But UNION ALL cannot be so re-written, and that, I think, is a good argument for having this optimization in SQLite while not necessarily having the others. I've been tempted before to write at least the objectifying parser for this. > At http://www.sqlite.org/src/info/7af3acbbd4 there is a patch that adds the > optimization to avoid using a temp table for your UNION ALL queries. This > one small patch represents about 12 hours of intense work, so far. Much > more work will be required to get the patch performing to our release > standards. All of this effort on your behalf you are receiving for free. > In return, we ask two things: We all appreciate this very much. It is a lot of work, I know. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
