Oracle 和 MIcrosoft SQL 的不同[2]

a materialized view is like a combination of a table and a view.  like a view, it is defined as a logical view into the data of one or more tables.  when you update the tables, subsequent queries of the view see the updated data.  however, like a table, its data is stored in the database.  also, like a table, it is faster if you define indexes for it.

a regular view is stored as a mapping of data from tables.  when you modify the data in the tables, the view is completely ignored.  when you access the view, it joins the data currently in the tables, and returns the data you requested.  a materialized view is stored as such a mapping along with a copy of the actual data from the tables.  when you modify the data in the tables, the view's copy of the data is also updated.  when you access the view, the data is drawn directly from the copy.

thus a materialized view makes table updates a little slower, but makes view queries much faster.  it also consumes additional space in the database.

you could accomplish the same effect by defining an additional table instead of the view, and using triggers on the component tables to update it each time they are changed.  however, using a materialized view is more convenient, more efficient, and clearer to the next person who has to maintain your database.

thanks to andy glick for sending me a sample of a materialized view from his application!

--fred