Menu
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?
915
According to the SQLite official document, SQLite supports 'Materializations Of Views And Subqueries' by storing the subquery results in a temporary table.
When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects.
Creating a materialized view requires CREATE MATERIALIZED VIEW privilege on the schema, and SELECT privilege on the base table. For more information about privileges and materialized views, see Privileges on a Materialized View's Schema.
SQLite CREATE VIEW statement First, specify a name for the view. The IF NOT EXISTS option only creates a new view if it doesn't exist. If the view already exists, it does nothing. Second, use the the TEMP or TEMPORARY option if you want the view to be only visible in the current database connection.
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. [...]
57
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
