Sorry i didn't realize before that you had:
select * from (... union all ...)
Try with a count(*) as such:
select count(*) from (... union all ...)
And you'll see that both union and "union all" will create a temp file.
Union needs the temp file to remove the duplicates. Union All doesn't
need it at all.
l.
On 29/10/2012 10:37 μμ, Clemens Ladisch wrote:
Eleytherios Stamatogiannakis wrote:
My understanding (and what my experiments have shown) is that in both
cases "COMPOUND SUBQUERIES 1 AND 2" will write a temp file to /var/tmp.
Okay, let's create a test database ...
$ strace -e trace=open sqlite3 test.db
[...]
sqlite> create table t(x);
open("/tmp/test.db-journal", O_RDWR|O_CREAT|O_CLOEXEC, 0644) = 4
open("/dev/urandom", O_RDONLY|O_CLOEXEC) = 5
open("/tmp", O_RDONLY|O_CLOEXEC) = 5
sqlite> begin;
sqlite> insert into t values('looooooooooooooooong string');
open("/tmp/test.db-journal", O_RDWR|O_CREAT|O_CLOEXEC, 0644) = 4
sqlite> insert into t select * from t;
sqlite> insert into t select * from t;
sqlite> insert into t select * from t;
... that becomes so big that the subquery overflows the cache:
[...]
sqlite> insert into t select * from t;
open("/var/tmp/etilqs_Oekg82a6826YGdz",
O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0600) = 5
sqlite> commit;
Only the UNION query claims to use temporary storage for the subquery:
(These two queries are constructed so that they have no result records.)
sqlite> explain query plan select * from (select rowid, x from t union all
select rowid, x from t) where x = '';
1|0|0|SCAN TABLE t (~100000 rows)
2|0|0|SCAN TABLE t (~100000 rows)
0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)
sqlite> explain query plan select * from (select rowid, x from t union
select rowid, x from t) where x = '';
2|0|0|SCAN TABLE t (~1000000 rows)
3|0|0|SCAN TABLE t (~1000000 rows)
1|0|0|COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)
0|0|0|SCAN SUBQUERY 1 (~200000 rows)
When executing them, only UNION uses temporary files:
sqlite> select * from (select rowid, x from t union all select rowid, x from
t) where x = '';
sqlite> select * from (select rowid, x from t union select rowid, x from t)
where x = '';
open("/var/tmp/etilqs_QNvTpzSHSedfFFM",
O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0600) = 4
open("/var/tmp/etilqs_RiTrAL6vrIxpnOu",
O_RDWR|O_CREAT|O_EXCL|O_NOFOLLOW|O_CLOEXEC, 0600) = 5
Now, what UNION ALL query wants to use a temporary table?
I'd guess that SQLite needs to save the result for some other reasons.
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
