What is a materialized view?
Answers were Sorted based on User's Feedback
In a database management system following the relational
model, a view is a virtual table representing the result of
a database query. Whenever an ordinary view's table is
queried or updated, the DBMS converts these into queries or
updates against the underlying base tables. A materialized
view takes a different approach in which the query result
is cached as a concrete table that may be updated from the
original base tables from time to time. This enables much
more efficient access, at the cost of some data being
potentially out-of-date. It is most useful in data
warehousing scenarios, where frequent queries of the actual
base tables can be extremely expensive.
In addition, because the view is manifested as a real
table, anything that can be done to a real table can be
done to it, most importantly building indexes on any
column, enabling drastic speedups in query time. In a
normal view, it's typically only possible to exploit
indexes on columns that come directly from (or have a
mapping to) indexed columns in the base tables; often this
functionality is not offered at all.
Materialized views were implemented first by the Oracle
database.
There are three types of materialized views:
1) Read only
Cannot be updated and complex materialized views are
supported
2) Updateable
Can be updated even when disconnected from the master site.
Are refreshed on demand.
Consumes fewer resources.
Requires Advanced Replication option to be installed.
3) Writeable
Created with the for update clause.
Changes are lost when view is refreshed.
Requires Advanced Replication option to be installed
Is This Answer Correct ? | 3 Yes | 0 No |
**A materialized view provides indirect access to table data
by storing the results of a query in a separate schema
object. Unlike an ordinary view, which does not take up any
storage space or contain any data.
**You can define a materialized view on a base table,
partitioned table or view and you can define indexes on a
materialized view.
**Materialized views are schema objects that can be used to
summarize, precompute, replicate, and distribute data. E.g.
to construct a data warehouse
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / priya
join indexes are called as materialized view.these are created for frequently joining columns to improve the join query performance.
Is This Answer Correct ? | 0 Yes | 0 No |
How to override dml statements with triggers?
What is apply operator in sql?
How to change the password of a login name in ms sql server?
How will oyu test the stored procedure taking two parameters namely first name and last name returning full name?
How are the exceptions handled in sql server programming?
What is data source document?
I have a website that allows customers to browse and place orders for certain products. I have 2 tables; Customers and Orders. The Customers table holds the customer records and the Orders table holds the orders placed by the customer. Both tables are tied based on the field Cust_ID. Example of the data is shown below: Cust_ID Cust_Name Cust_ID Product Amount Order_Date 1001 John Tan 1001 P-5211 $120.00 2/13/2006 1002 Michael Wong 1001 K-1428 $88.90 1/11/2006 1003 Mary Cheong 1003 C-0923 $82.50 1/27/2006 1004 Ahmad Suffian 1003 K-1428 $88.90 2/2/2006 Write a single SQL statement that extracts all purchase records with the following criteria: 1. Customer names starting with “M” only. 2. Orders placed within the current month only. 3. Amount does not exceed $100.00 The list must be sorted by order date with the latest order showing on top.
What is the difference between having and where clause?
Explain identity in sql server?
please bar with my english i having a database called tblhallreservation in which res_date is date field has to select all the fields in table deponding on month either has to display all details for the month jan or feb and so on
what are the disadvantages of cursors? : Sql server database administration
What happens when converting big values to numeric data types?