Free Newsletters:
Database Journal  
DBAnews  

DBASupport

 The Knowledge Center for Oracle Professionals

Search DBAsupport:
 
HOME 11g Central 10g Central 9i Central 8i Central Oracle News Scripts FAQ OCP Zone Resources Technical Docs Tools & Utilities Forums
internet.com

» HOME
» FEATURES
    11g Central
    10g Central
    9i Central
    8i Central
    Oracle News
» COMMUNITY
    Scripts
    Forums
    FAQ
    OCP Zone
» RESOURCES
    Resources
    Technical Docs
    Tools & Utilities
    Tech Jobs

Marketplace Partners
Become a Marketplace Partner


internet.commerce
Be a Commerce Partner












internet.com
IT
Developer
Internet News
Small Business
Personal Technology
International

Search internet.com
Advertise
Corporate Info
Newsletters
Tech Jobs
E-mail Offers


   DBAsupport.com > Oracle > Oracle 9i Central > Featured Stories




KRONOS Technical Analyst
Professional Technical Resources
US-OR-Portland

Justtechjobs.com Post A Job | Post A Resume

Materialized Views in Oracle
Ajay Gursahani, ajay_gursahani@yahoo.com


A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table’s data. Materialized views, which store data based on remote tables are also, know as snapshots.

A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).

For replication purposes, materialized views allow you to maintain copies of remote data on your local node. These copies are read-only. If you want to update the local copies, you have to use the Advanced Replication feature. You can select data from a materialized view as you would from a table or view.

For data warehousing purposes, the materialized views commonly created are aggregate views, single-table aggregate views, and join views.

In this article, we shall see how to create a Materialized View and discuss Refresh Option of the view.

In replication environments, the materialized views commonly created are primary key, rowid, and subquery materialized views.

Primary Key Materialized Views

The following statement creates the primary-key materialized view on the table emp located on a remote database.

SQL7gt; 	CREATE MATERIALIZED VIEW mv_emp_pk
   	REFRESH FAST START WITH SYSDATE 
	NEXT  SYSDATE + 1/48
	WITH PRIMARY KEY 
	AS SELECT * FROM emp@remote_db;

Materialized view created.

Note: When you create a materialized view using the FAST option you will need to create a view log on the master tables(s) as shown below:

SQL> CREATE MATERIALIZED VIEW LOG ON emp;
Materialized view log created.

Rowid Materialized Views

The following statement creates the rowid materialized view on table emp located on a remote database:

SQL> 	CREATE MATERIALIZED VIEW mv_emp_rowid 
	REFRESH WITH ROWID 
   	AS SELECT * FROM emp@remote_db; 

Materialized view log created.

Subquery Materialized Views

The following statement creates a subquery materialized view based on the emp and dept tables located on the remote database:

SQL> CREATE MATERIALIZED VIEW  mv_empdept
AS SELECT * FROM emp@remote_db e
WHERE EXISTS
     (SELECT * FROM dept@remote_db d
     WHERE e.dept_no = d.dept_no)

Next


Back to
DBAsupport.com





internet.comearthweb.comDevx.commediabistro.comGraphics.com

Search:

Jupitermedia Corporation has two divisions: Jupiterimages and JupiterOnlineMedia

Jupitermedia Corporate Info

Legal Notices, Licensing, Reprints, Permissions, Privacy Policy.
Advertise | Newsletters | Tech Jobs | Shopping | E-mail Offers

Whitepapers and eBooks

Intel Whitepaper: Comparing Two- and Four-Socket Platforms for Server Virtualization
IBM Solutions Brief: Go Green With IBM System xTM And Intel
HP eBook: Simplifying SQL Server Management
IBM Contest: Are You the Next Superstar? Join the "Search for the XML Superstar" Contest to Find Out
Microsoft PDF: Top 10 Reasons to Move to Server Virtualization with Hyper-V
Microsoft PDF: Six Reasons Why Microsoft's Hyper-V Will Overtake Vmware
Microsoft Step-by-Step Guide: Hyper-V and Failover Clustering
Intel PDF: Quad-Core Impacts More Than the Data Center
Intel PDF: Virtualization Delivers Data Center Efficiency
Go Parallel Article: PDC 2008 in Review
Microsoft PDF: Top 11 Reasons to Upgrade to Windows Server 2008
Avaya Article: Communication-Enabled Mashups: Empowering Both Business Owners and IT
Intel Whitepaper: Building a Real-World Model to Assess Virtualization Platforms
  PDF: Intel Centrino Duo Processor Technology with Intel Core2 Duo Processor
Microsoft Article: Build and Run Virtual Machines with Hyper-V Server 2008
Go Parallel Article: Q&A with a TBB Junkie
IBM Whitepaper: Innovative Collaboration to Advance Your Business
Internet.com eBook: Real Life Rails
IBM eBook: The Pros and Cons of Outsourcing
Internet.com eBook: Best Practices for Developing a Web Site
IBM CXO Whitepaper: The 2008 Global CEO Study "The Enterprise of the Future"
Avaya Article: Call Control XML in Action - A CCXML Auto Attendant
IBM CXO Whitepaper: Unlocking the DNA of the Adaptable Workforce--The Global Human Capital Study 2008
Adobe Acrobat Connect Pro: Web Conferencing and eLearning Whitepapers
HP eBook: Guide to Storage Networking
MORE WHITEPAPERS, EBOOKS, AND ARTICLES