On Jan 10, 2010, at 4:50 AM, Max Vlasov wrote: > Documentation says that INTERSECT implemented with temporary tables > either > in memory or on disk. Is it always the case?
No. If there is an ORDER BY clause, SQLite may run each subquery as a separate co-routine and merge the results. If the ORDER BY on both subqueries can be computed using indices, then the INTERSECT will run in either linear or logarithmic time (depending on what indices are available) and in constant space. This is also true of UNION and EXCEPT. If there is an ORDER BY clause but there does not exist indices needed to implement the ORDER BY for one or both subqueries, then the subquery might get evaluated into a temporary table, and sorted there, prior to the merge step. I *think* it will always be the case that if an INTERSECT query has an ORDER BY clause and if sqlite3_stmt_status(db, SQLITE_STMTSTATUS_SORT, 1) returns 0, then the query does not use temporary tables and runs in constant space. But I might be wrong. And in any event, that rule is subject to change in a future release if we decide we can get better performance by doing things differently. D. Richard Hipp d...@hwaci.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
