| Snapshots [message #75043] |
Sat, 21 April 2001 08:49  |
Raghu
Messages: 31
Registered: September 2000
|
Member |
|
|
Can anybody tell me the difference between snapshots and Materialized views and necessity of introducing materialized views.
----------------------------------------------------------------------
|
|
|
|
| Re: Snapshots [message #75045 is a reply to message #75043] |
Tue, 24 April 2001 23:15  |
yushuai
Messages: 5
Registered: December 2000
|
Junior Member |
|
|
There is no difference between them!
----------------------------------------------------------------------
|
|
|
|
| Re: Snapshots [message #75053 is a reply to message #75045] |
Wed, 23 May 2001 01:50 |
ramesh giri .n
Messages: 3
Registered: May 2001
|
Junior Member |
|
|
What is the purpose of a materialized view?
Before beginning any exploration of the how of the materialized view, it is important to understand the why behind this new data structure.
The materialized view is another in a long line of gofast tricks implemented in the Oracle database. Introduced with Oracle8i, a materialized view is designed to improve performance of the database by doing some intensive work in advance of the results of that work being needed.
In the case of a materialized view, the data for the view is assembled when the view is created or refreshed. Later queries that need this data automatically use the materialized view, thus saving the overhead of performing the work already done by the view.
The work avoided by a materialized view is essentially twofold:
A materialized view can be used to pre-collect aggregate values.
A materialized view can be used to assemble data that would come from many different tables, which would in turn require many different joins to be performed.
You may recognize these two requirements as being characteristic of data warehousing. And, in fact, the materialized view is most frequently used in data warehouse applications. Data warehousing applications also do not typically require access to real-time data, so the need to assemble data into a materialized view does not necessarily impact the usefulness of the view.
What is a materialized view?
In Oracle8, a new type of database structure, the materialized view, was introduced. A materialized view is a polyglot structure, resembling other existing structures in some ways, yet different in others.
A materialized view is like a view in that it represents data that is contained in other database tables and views; yet it is unlike a view in that it contains actual data. A materialized view is like an index in that the data it contains is derived from the data in database tables and views; yet unlike an index in that its data must be explicitly refreshed. Finally, a materialized view is very much like a snapshot in that an administrator can specify when the data is to be refreshed; but it is unlike a snapshot in that a materialized view should either include summary data or data from many different joined tables.
How is a materialized view created?
Since a materialized view is just another data structure, you create a materialized view with a piece of Data Definition Language (DDL) syntax.
The syntax used is made up of several parts and keywords. The most commonly used syntax options are described in the following table:
CREATE MATERIALIZED VIEW are required keywords
name is the qualified name of the materialized view
Physical attributes clause allows you to specify the physical attributes, such the tablespace name, for the materialized view
BUILD clause The BUILD clause allows you to specify when you want to build the actual data in the table. Your options are BUILD IMMEDIATE, which calls for the view to be immediately built, BUILD DEFERRED, which calls for the view to be built when it is first refreshed (see explanation of REFRESH clause below) or ON PREBUILT TABLE, which indicates that you are identifying a table that is already built as a materialized view.
REFRESH clause Since the materialized view is built on underlying data that is periodically changed, you must specify how and when you want to refresh the data in the view. You can specify that you want a FAST refresh, which will only update the values in the materialized view, assuming that some preconditions are met, COMPLETE, which recreates the view completely, or FORCE, which will do a FAST refresh if possible and a COMPLETE refresh if the preconditions for a FAST refresh are not available.
The REFRESH clause also contains either the keywords ON COMMIT, which will cause a refresh to occur whenever the underlying data is changed and the change is committed, or ON DEMAND, which will only perform a refresh when it is scheduled or explicitly called. You can also use keywords in the REFRESH clause to create a schedule for recurring refresh operations.
AS subquery The last clause of the CREATE MATERIALIZED VIEW command contains the sub-query that will be used to retrieve the data that will compose the materialized view.
There is one more clause associated with the creation of materialized views, but before you can understand this clause, you must gain a deeper understanding of how a materialized view is used by your Oracle8i database.
----------------------------------------------------------------------
|
|
|
|
| Snapshot Tutorial [message #75092 is a reply to message #75045] |
Fri, 03 August 2001 03:07 |
syam
Messages: 5
Registered: August 2001
|
Junior Member |
|
|
Can any body tell me where can i find some study materials for snapshots on the web.
Thanks in advance.
----------------------------------------------------------------------
|
|
|
|
| Re: Snapshots [message #75111 is a reply to message #75045] |
Fri, 05 October 2001 05:42 |
Sameer Kamath
Messages: 1
Registered: October 2001
|
Junior Member |
|
|
I have created a materialized View , i want
to scheduled that materialized view .
How Can i do that ?
Please give me some coding regarding this issue or scripts ...
Regds
Sameer
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
