View is not flattened when inside an IN sub-query (incl complete sql)

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

View is not flattened when inside an IN sub-query (incl complete sql)

E.Pasma
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users