How can a materialized view be created in sqlite?

问题:

I've done countless searches on materialized views and SQLite. Of what I can find there seems to be mentions in 2004 and 2006 that SQLite DOES NOT have materialized views. Followed immediately by SQLite's changelog from March 2008 where it specifically mentions optimizing materialized views.

Now, I figure logically either the 2004 and 2006 are outdated, or the 2008 changelog is wrong.

Any idea which it is?

If materialized views ARE now in SQLite, how are they created?

回答1:

I'd say what other DBMSes call "materialized views" are not supported, and what that ChangeLog meant is what MySQL's EXPLAIN would call a "filesort"; from the tempfiles page:

2.5 Materializations Of Views And Subqueries

Queries that contain subqueries must sometime evaluate the subqueries separately and store the results in a temporary table, then use the content of the temporary table to evaluate the outer query. We call this "materializing" the subquery. [...]