Friday, July 28, 2006

Materialized views ch1

Database Link:

1) Private DB Link

create database link remote_connect

connect to sbiswas identified by sbiswas

using 'remote_conn_str'


2) Shared DB Link

<>create shared database link x_connect_shared connect to current_user

authenticated by sbiswas identified by sbiswas

using 'connstrl'

Then from local database you can access the remote one as:

select * from dual@"raman_connect"

What is a materialized view?

Introduced in Oracle 8

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.

Materialized view and CBO

The cost-based optimizer can automatically substitute a materialized view for a standard table or group of tables if appropriate. This capability in the optimizer means that you can add materialized views to your database and reap the performance benefits, without having to rewrite any of your applications.

The cost-based optimizer will examine a particular query to see if it can be satisfied by a materialized view. If it can, the cost-based optimizer will simply use the materialized view instead of the original data structures. The cost-based optimizer has some intelligence, so even if the data in a materialized view can only satisfy part of the query, the optimizer will still use the materialized view for as much of the data as possible.

If you want a materialized view to be considered for use by the cost-based optimizer, you must include the keyword phrase ENABLE QUERY REWRITE in your specification of the view, or as part of an ALTER VIEW statement after the materialized view has been created.

As with other potential choices for the cost-based optimizer, you can use a hint to explicitly direct a query to the materialized view.

Privileges needed for creating Materialized views or Snapshots

ON COMMIT REFRESH on the tables which are not owned by you.

COMMIT REFRESH on your tables.


Read only: Change in the materialized view cannot update the underlying tables

Same as “Create table as select ….”.


In an updatable materialized view, there’s less control over method by which rows in the materialized view are changed.

(i) Rows may be changed based on changes in the master table

(ii) Rows may be changed by users of the materialized views.

So data should flow in both the ways.
Since multiple sources of changes exist, multiple masters exist (referred as a multimaster configuration).
If you use updatable materialized views, you need to treat the materialized view as master, complete with all of the underlying replication structures and facilities normally found at master sites.

  1. Materialized Views for Data Warehouses :

Materialized views in these environments are often referred to as summaries, because they store summarized data. They can also be used to precompute joins with or without aggregations. A materialized view eliminates the overhead associated with expensive joins and aggregations for a large or important class of queries.

  1. Materialized Views for Distributed Computing

In distributed environments, you can use materialized views to replicate data at distributed sites and to synchronize updates done at those sites with conflict resolution methods. These replica materialized views provide local access to data that otherwise would have to be accessed from remote sites. Materialized views are also useful in remote data marts.

3.Materialized Views for Mobile Computing

You can also use materialized views to download a subset of data from central servers to mobile clients, with periodic refreshes and updates between clients and the central servers.

Materialized view and query rewrite

If a materialized view is to be used by query rewrite, it must be stored in the same database as the detail tables on which it relies.

A materialized view can be partitioned, and you can define a materialized view on a partitioned table.

You can also define one or more indexes on the materialized view.

It is recommended that you try to avoid writing SQL statements that directly reference the materialized view, because then it is difficult to change them without affecting the application. Instead, let query rewrite transparently rewrite your query to use the materialized view.

No comments:

Post a Comment