Home » Server Options » Replication » Difference between snapshot and materialized view
icon9.gif  Difference between snapshot and materialized view [message #116538] Wed, 20 April 2005 09:57 Go to next message
d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta
Senior Member
Hi bosses,
What is the difference between snapshot view and materialized view.I know one before oracle 8.x it was snapshot.Apart from this what else.

Thanks
Dinesh
Re: Difference between snapshot and materialized view [message #116581 is a reply to message #116538] Wed, 20 April 2005 13:33 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
No difference.
Re: Difference between snapshot and materialized view [message #121879 is a reply to message #116538] Wed, 01 June 2005 10:32 Go to previous messageGo to next message
tarun123v
Messages: 9
Registered: March 2005
Location: Hyderabad(India)
Junior Member

Who told u Tedd that there is no Difference between Snapshot and Materialized Views
Read Below content to understand




This is an edited excerpt from the book ORACLE8i DBA Handbook by Kevin Loney and Marlene Theriault, copyright Osborne/McGraw-Hill, reprinted by permission. See http://www.kevinloney.com for additional articles and news.

The underlying technology for materialized views should be familiar to anyone who has created and maintained snapshots. A materialized view is a table that stores derived data. During its creation, you specify the SQL used to populate the materialized view.

For a large database, a materialized view may offer several performance advantages. Depending on the complexity of the base SQL, you may be able to populate the materialized view with incremental changes (via a materialized view log) instead of completely re-creating it during data refreshes.

Unlike snapshots, materialized views can be used dynamically by the optimizer to change the execution paths for queries. This feature, called query rewrite, enables the optimizer to use a materialized view in place of the table queried by the materialized view, even if the materialized view is not named in the query. For example, if you have a large SALES table, you may create a materialized view that sums the SALES data by region. If a user queries the SALES table for the sum of the SALES data for a region, ORACLE can redirect that query to use your materialized view in place of the SALES table. As a result, you can reduce the number of accesses against your largest tables, improving the system performance.

To enable a materialized view for query rewrite, all of the master tables for the materialized view must be in the materialized view’s schema, and you must have the QUERY REWRITE system privilege. If the view and the tables are in separate schemas, you must have the GLOBAL QUERY REWRITE system privilege. In general, you should create materialized views in the same schema as the tables on which they are based; otherwise, you will need to manage the permissions and grants required to create and maintain the materialized view.

Like a snapshot, a materialized view creates a local table to store the data and a view that accesses that data. Depending on the complexity of the materialized view, ORACLE may also create an index on the materialized view’s local table. You can index the materialized view’s local table to improve the performance of queries against the materialized view.

To create a materialized view, use the create materialized view command. The example shown in the following listing creates a materialized view against the SALES table:

create materialized view SALES_MONTH_MV
tablespace AGG_DATA
refresh complete
start with sysdate
next sysdate+1
enable query rewrite
as
select Sales_Month, SUM(Amount)
from SALES
group by Sales_Month;

As shown in the preceding listing, the create materialized view command specifies the name of the view and its refresh schedule. In this example, a complete refresh of the view is chosen-each time the view is refreshed, its data will be completely deleted and re-created. For views that are not based on aggregations, you can use fast refreshes in combination with materialized view logs to send only incremental changes to the materialized view. The start with and next clauses tell ORACLE when to schedule refreshes of the data. The data will be automatically refreshed if you have enabled background job processes (via the JOB_QUEUE_PROCESSES init.ora parameter). The tablespace clause tells ORACLE where to store the local table for the materialized view. The enable query rewrite clause enables the optimizer to redirect queries of SALES to SALES_MONTH_MV if appropriate.

The full syntax for creating materialized views is found online in Oracle8i SQL Reference (requires a Technet login) .

Fast refreshes of materialized views use materialized view logs. A materialized view log is a table stored along with the master table for the materialized view. As rows change in the master table, the changes are written to the materialized view log. During a fast refresh, the changed rows from the master table, as identified via the materialized view log, are sent to the materialized view. If the changes account for less than 25 percent of the rows in the master table, a fast refresh is generally faster than a complete refresh. For the full syntax of the create materialized view log command, see Oracle8i SQL Reference (requires a Technet login) .
Re: Difference between snapshot and materialized view [message #128132 is a reply to message #121879] Fri, 15 July 2005 05:47 Go to previous messageGo to next message
d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta
Senior Member
Thanks all,

Then i can say that the only difference is query rewrite any thing else.

Regds
dinesh
Re: Difference between snapshot and materialized view [message #134511 is a reply to message #116538] Thu, 25 August 2005 06:03 Go to previous messageGo to next message
sharan_it
Messages: 140
Registered: July 2005
Location: Chennai
Senior Member
Better u say Snapshot is redefined as Materialized view and Query rewrite feature is added from Oracle 8i
Re: Difference between snapshot and materialized view [message #134512 is a reply to message #116538] Thu, 25 August 2005 06:06 Go to previous messageGo to next message
sharan_it
Messages: 140
Registered: July 2005
Location: Chennai
Senior Member
Before Oracle 8i whatever u create Snapshots or MVs it shows Snapshot created.
From Oracle 8i and later versions,if u create Snapshots or Materialized views it will show Materialized View created
Re: Difference between snapshot and materialized view [message #135549 is a reply to message #134512] Thu, 01 September 2005 03:29 Go to previous messageGo to next message
d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta
Senior Member
Ok...
Thanks

Regds
Dinesh
Re: Difference between snapshot and materialized view [message #221903 is a reply to message #116538] Wed, 28 February 2007 23:52 Go to previous messageGo to next message
vivekg19
Messages: 2
Registered: February 2007
Junior Member
hi how to create index on materialiez view
Re: Difference between snapshot and materialized view [message #222359 is a reply to message #221903] Sat, 03 March 2007 04:59 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Using the CREATE INDEX command.
Previous Topic: Article on CD DvD Replication
Next Topic: Replication on SE
Goto Forum:
  


Current Time: Thu Mar 28 03:49:23 CDT 2024