Documentum : DBLink, View, Materialized View, Register Table

A post concerning the user of DbLink, View, and Register Table in Documentum.

Presentation of DBLink
In a database management system (DBMS), such as Oracle Database, a DBLink, or database link, is a database object that allows queries to be executed on another database, is physically on the same machine or is remote.
A DBLink is a schema object that causes Oracle to connect to a remote database to access it. This is a local pointer that allows the user to access objects in a remote database.
There are three types of DBLinks:

Private DBLinks : It is the type of DBLink that is created by default when the user does not specify the type, especially when it does not specify the PUBLIC keyword when creating it. Only the user who created this link can then use it;

Public DBLinks : This is the type of DBLink that is created when the user specifies the type by the PUBLIC keyword. In this case, this link will be usable by other users;

The CONNECT TO option allows access to the remote database with a different user name than the current one in the local database session.

is the name of the database to which the DBLink points, if the GLOBAL_NAMES parameter is ‘true’. Otherwise, any identifier can be used.

is a valid SQL * NET connection string (for example, under Oracle, found in the tnsnames.ora file).

To then use a database link, simply specify the link used, preceded by the symbol ‘@’, before the identifier of the remote object.

For example, to obtain the list of client codes (CLIENT_CODE of MYSCHEMA schema) of the CLIENTS table located on another instance (accessible by the link named MY_DB_LINK):

select CLIENT_CODE from MYSCHEMA.CLIENTS@MY_DB_LINK;

Presentation of View
A view in a database is a synthesis of a request to query the database. It can be seen as a virtual table, defined by a query.
Views are not necessarily purely virtual. Some DBMSs like Oracle Database can store the view on disk, it becomes a real cache system.

Views are created with the CREATE VIEW command. An example, where a company’s employees are in a table, the departments of the company in another and where one has to make a join to display the name of the department with the employee:

The above query SELECT can be used much more simply, the join will be no longer visible:

SELECT * FROM ViewEveryBody;

The views are used almost as tables (they can be in a FROM clause of a SELECT, UPDATE, etc) with some restrictions, which depend on the DBMS.

In relational database systems, a view is a virtual table representing the result of a query on the database. As the name suggests and unlike a standard view, in a materialized view the data is duplicated. It is used primarily for optimization and performance purposes in the case where the associated query is particularly complex or cumbersome, or to make table replications.
The freshness of the data in the materialized view depends on the options chosen when it is created. The offset between the data in the master table and the materialized view can be zero (synchronous refresh) or a scheduled duration: time, day, and so on. Depending on the context, there are different types of materialized view: on primary key, rowid, and more or less complex: with aggregation functions, subqueries, joins, etc.

SQL Queries

Minimal syntax for creating a materialized view under Oracle:

CREATE MATERIALIZED VIEW MV1 AS SELECT * FROM scott.emp

Query to create a materialized view with accurate refresh rate under Oracle: