Still using SQLite 3.7.14: I have two identically declared virtual tables that differ only in the backing store (memory section vs. CTree files), and a view.
CREATE VIRTUAL TABLE pools_MM using Memory(); CREATE VIRTUAL TABLE pools_CT using CTree(); CREATE VIEW pools_VW AS SELECT * FROM pools_MM UNION ALL SELECT * FROM pools_CT; Both tables support an index with 9 fields (a, b, c, d, e, f, g, h, i). There are also fields corresponding to monetary values (pi, ca, rb, crb). When running the following statement (note that key field d is not used) SELECT SUM(pi - ca - rb + crb) FROM pools_VW where a=7 and b=9 and c=2448 and e=1 and g=2 and f IN (1,2,4,5); SQLite is materializing the view via full table scans, which makes it run very slowly. The query plan looks like this sele order from deta ---- ------------- ---- ---- 2 0 0 SCAN TABLE pools_MM VIRTUAL TABLE INDEX 0: (~0 rows) 3 0 0 SCAN TABLE pools_CT VIRTUAL TABLE INDEX 0: (~0 rows) 1 0 0 COMPOUND SUBQUERIES 2 AND 3 (UNION ALL) 0 0 0 SCAN SUBQUERY 1 (~1 rows) 0 0 0 EXECUTE LIST SUBQUERY 4 Or, expressed as a sequence of SELECTS: CREATE TEMP TABLE T1 AS SELECT * FROM pools_MM; INSERT INTO T1 SELECT * FROM pools_CT; CREATE TEMPT TABLE T2 (f integer); INSERT INTO T2 VALUES (1),(2),(4),(5); SELECT SUM(pi - ca - rb + crb) FROM T1 where a=7 and b=9 and c=2448 and e=1 and g=2 and f IN (SELECT f FROM T2); How can I coax SQLite into selecting/computing from the "view members" without completely rewriting the statement? ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
