Transcript Materialized views (snapshot tables)

Materialized views
(snapshot tables)
Using Oracle
Materialized views
1
Ordinary views vs. materialized
views
• Ordinary views
– Virtual table
– Named select
statement
• Part of the SQL
standard
• Syntax
– CREATE VIEW
viewName AS
selectStatement
• Physical table
– Replication of master
data at a single point in
time
• Not part of the SQL
standard
• Syntax
– CREATE
MATERIALIZED VIEW
viewName AS
selectStatement
Materialized views
2
Why use materialized views?
• Replicate data to non-master sites
– To save network traffic when data is used in
transactions
• Cache expensive queries
– Expensive in terms of time or memory
– Example: Sum, average or other calculations
on large amounts of data
Materialized views
3
Refreshing a materialized view
• Refresh types
– Complete refresh
• Recreates the materialized view
– Fast refresh
• Only changed data is refreshed
• Initiating a refresh
– Scheduled refresh
– On-demand refresh
• execute DBMS_REFRESH.REFRESH('hr_refg')
Materialized views
4
Different kinds of materialized
views
• Read-only
– Insert, update or delete NOT allowed
• Updateable
– Insert, update and delete on the view is allowed
– Changes made to the view are pushed back to the master tables at
refresh
– SQL syntax
• CREATE MATERIALIZED VIEW viewName FOR UPDATE AS
aSelectStatement
• Writeable
– Insert, update and delete on the view is allowed
– Changes made to the view are NOT pushed back to the master
tables at refresh
– SQL syntax
• Same as updateable. The difference lies in the definitio of “refresh
groups” …
Materialized views
5
References and further reading
• Oracle9i Advanced Replication
Release 2 (9.2)
– 3 Materialized View Concepts and Architecture
• http://download.oracle.com/docs/cd/B10500_01/server.92
0/a96567/repmview.htm
• Oracle® Database SQL Reference
10g Release 2 (10.2)
– CREATE MATERIALIZED VIEW
• http://downloaduk.oracle.com/docs/cd/B19306_01/server.102/b14200/stat
ements_6002.htm
Materialized views
6