Nikolaus Rath <nikol...@rath.org> wrote: > However, if I use an intermediate view: > > sqlite>CREATE VIEW inode_blocks_v AS > SELECT * FROM inode_blocks > UNION > SELECT id as inode, 0 as blockno, block_id FROM inodes WHERE block_id > IS NOT NULL > > and then run the same query on the view, SQLite scans through all > involved tables:
A select from the view is transformed into SELECT 1 FROM (SELECT * FROM inode_blocks UNION SELECT id as inode, 0 as blockno, block_id FROM inodes WHERE block_id IS NOT NULL ) WHERE inode=42; SQLite's optimizer isn't really that smart - definitely not smart enough to move the condition into the sub-select and duplicate it into each subquery. That's a rather non-trivial transformation. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
