Hi,
First, think you for doing sqlite. I like it !
I am surprised by the result of "EXPLAIN QUERY PLAN" on some queries.
I will explain try to explain my issue.
1-Open the attached database
This database contains a table "a":
CREATE TABLE a(id INT,v REAL,r_a_id INT)
This table "a" is pointing to an other table named "c" through the
attribute "r_a_id":
CREATE TABLE c(id INT,name)
The view "v_c" is representing that table "c" with one more column
"total" computed by a SELECT on "a":
CREATE VIEW v_c AS SELECT *, (SELECT TOTAL(a.v) FROM a WHERE
a.r_a_id=c.id ) AS total FROM c
2-EXPLAIN QUERY PLAN SELECT id FROM v_c
returns:
SCAN TABLE c (~74 rows)
This is correct
3-EXPLAIN QUERY PLAN SELECT id FROM v_c
returns:
SCAN TABLE c (~74 rows)
EXECUTE CORRELATED SCALAR SUBQUERY 1
SEARCH TABLE a USING INDEX a_r_a_id (r_a_id=?) (~88 rows)
This is correct too because we have to compute the "total" column
4-EXPLAIN QUERY PLAN SELECT total, total+1, total+2 FROM v_c
returns:
SCAN TABLE c (~74 rows)
EXECUTE CORRELATED SCALAR SUBQUERY 1
SEARCH TABLE a USING INDEX a_r_a_id (r_a_id=?) (~88 rows)
EXECUTE CORRELATED SCALAR SUBQUERY 2
SEARCH TABLE a USING INDEX a_r_a_id (r_a_id=?) (~88 rows)
EXECUTE CORRELATED SCALAR SUBQUERY 3
SEARCH TABLE a USING INDEX a_r_a_id (r_a_id=?) (~88 rows)
For me this is not optimized (but I am not a db specialist) because the
computation of "total" is done 3 times instead of only one.
What do you think about that ?
Is it a bug in the plan of execution or is it normal ?
Do you know I can optimize my view to have only one "CORRELATED SCALAR" ?
I tried "EXPLAIN QUERY PLAN SELECT total, total+1, total+2 FROM (select total
from v_c)" but I have the same result.
In advance, thank you for your help and your very good tool !
Regards,
Stéphane MANKOWSKI
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
