Infolinks

Friday 29 June 2012

Views, Materialized Views and Synonyms

Views, Materialized Views and Synonyms

Views
A view takes the output of a query and makes it appear like a virtual table. You can use a view in most places where a table can be used.
All operations performed on a view will affect data in the base table and so are subject to the integrity constraints and triggers of the base table.
A View can be used to simplify SQL statements for the user or to isolate an application from any future change to the base table definition. A View can also be used to improve security by restricting access to a predetermined set of rows or columns.
In addition to operating on base tables, one View can be based on another, a view can also JOIN a view with a table (GROUP BY or UNION).
Using views encourages the use of Shared SQL with the benefit of reduced memory usage.
Read-Only vs Updatable Views
The data dictionary views ALL_UPDATABLE_COLUMNS, DBA_UPDATABLE_COLUMNS,
and USER_UPDATABLE_COLUMNS indicate which view columns are updatable.
An updatable view lets you insert, update, and delete rows in the view and propagate the changes to the target master table.
In order to be updatable, a view cannot contain any of the following constructs:
SET or DISTINCT operators, an aggregate or analytic function, a GROUP BY, ORDER BY, CONNECT BY, or START WITH clause, a subquery (or collection expression) in a SELECT list
or finally (with some exceptions) a JOIN . See the Oracle Database Administrator's Guide for full details.

Views that are not updatable can be modified using an INSTEAD OF trigger.
Materialized Views
Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. E.g. to construct a data warehouse.
A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data.
The existence of a materialized view is transparent to SQL, but when used for query rewrites will improve the performance of SQL execution. An updatable materialized view lets you insert, update, and delete.
You can define a materialized view on a base table, partitioned table or view and you can define indexes on a materialized view.
A materialized view can be stored in the same database as its base table(s) or in a different database.
Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment.
A materialized view log is a schema object that records changes to a master table's data so that a materialized view defined on the master table can be refreshed incrementally.
Synonyms
A synonym is an alias for any table, view, materialized view, sequence, procedure, function, or package.
A public synonym is owned by the user group PUBLIC and every user in a database can access it.
A private synonym is in the schema of a specific user who has control over its availability to others.

Synonyms are used to:
- Mask the real name and owner of a schema object
- Provide global (public) access to a schema object
- Provide location transparency for tables, views, or program units of a remote database.
- Simplify SQL statements for database users
e.g. to query the table PATIENT_REFERRALS with SQL:
SELECT * FROM MySchema.PATIENT_REFERRALS;
CREATE PUBLIC SYNONYM referrals FOR MySchema.PATIENT_REFERRALS;
After the public synonym is created, you can query with a simple SQL statement:

SELECT * FROM referrals;
Heterogeneous Services
A database server component that allows access to a non-Oracle database using Oracle SQL.

"We always did feel the same,
We just saw it from a different point of view… - Bob Dylan, "Tangled Up In Blue"

No comments:

Post a Comment