Hello, below are two equivalent delete statements. The difference is
that the second version uses views, actually sub-queries, on the base
tables. These are simple one to one views that could be flattened out,
as in
http://www.sqlite.org/optoverview.html#flattening
The second query plan has a full scan on t2 instead of direct access
via the existing index sqlite_autoindex_t2_1. The view is apparently
not flattened. Is it possible changing this? Thanks, E. Pasma
.version
SQLite 3.21.0 2017-10-02 02:52:54
c9104b59c7ed360291f7f6fc8caae938e9840c77620d598e4096f78183bf807a
create table t1 (a, b, primary key(a,b));
create table t2 (b, c, primary key(b,c));
create table t3 (a, c, primary key(a,c));
.eqp on
delete from t3 where (a,c) in (
select a,c
from t1
join t2 using(b)
);
--EQP-- 0,0,0,SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (a=?
AND c=?)
--EQP-- 0,0,0,EXECUTE LIST SUBQUERY 0
--EQP-- 0,0,0,SCAN TABLE t1
--EQP-- 0,1,1,SEARCH TABLE t2 USING COVERING INDEX
sqlite_autoindex_t2_1 (b=?)
delete from t3 where (a,c) in (
select a,c
from (select a,b from t1)
join (select b,c from t2) using(b)
);
--EQP-- 0,0,0,SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (a=?
AND c=?)
--EQP-- 0,0,0,EXECUTE LIST SUBQUERY 0
--EQP-- 1,0,0,SCAN TABLE t1
--EQP-- 2,0,0,SCAN TABLE t2
--EQP-- 0,0,0,SCAN SUBQUERY 1
--EQP-- 0,1,1,SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX (b=?)
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
