Establishing a link from one database to another database is called a database link.
Using database links we can access the data existing in a remote database from a database.
Database links internally depends on oracle networking.
In order to create a database link, users should have 'create database link' privilege.
Note : Using database links, we can access the data and we can also perform DML operations but not DDL operations.
There are 2 types of datalinks :
1.private database link
2.public database link
In order to create a public database link, user should have 'create database link' privilege.
As the volume of data increases in the source table, accessing the data thru database links degrades the performance.
To overcome this problem oracle has introduced materialized views.
In older versions of oracle, materialized views are called snapshots.
In order to create a meterialized view user should have 'create materialized view' privilege.
Materialized view is a space demanding object and it is a local copy of a remote database object.
In order to refresh the materialized view, we need to set the parameter 'job_queue_processes'.
Once we set this parameter thru a value, that many number of CJQn(CoOrdinated Job Queue Processes)gets enabled depending on the volume of data.
From 10g, the parameter is set by default to a value 10.
CJQ0,CJQ1,CJQ2......CJQ9
At the time of creating a materialized view we can specify the refresh interval as well as the refresh mode.
Refresh interval represents how frequently the materialized view has to be refreshed.
The different types of refresh modes are
1.complete
2.fast
3.force
4.none
Complete means oracle truncates the entire materialized view and refreshes the data. Complete takes more time if the volume of data is very huge.
Fast means oracle refreshes the materialized view based on primary key column.In order to create a materialized view with fast option, source object must n should have primary key column. In order to refresh with fast option , at the source side we need to create materialized view log.
Force means, initially tries to refresh based on primary key, if primary key is not available it truncates the entire materialized view and refreshes.
None means no auto refresh.
We can also refresh the materialized view manually depending on the requirement by executing the following package :
SQL> exec dbms_mview.refresh('mview_name');
SQL> create materialized view dept_mview
refresh complete/fast
start with sysdate
next sysdate + (1/(24*60*60))
as select * from dept;
Sometimes we create materialized views to enable query rewrites within a database without depending on database links to improve the performance of SQL.
