Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Wednesday, 2 May 2012

Working with Simple and Complex Materialized Views

A materialized view is a replica of a target master from a single point in time. The master can be either a master table at a master site or a master materialized view at a materialized view site. Materialized views are updated from one or more masters through individual batch updates, known as a refreshes, from a single master site or master materialized view site.

Why Use Materialized Views?

Ease Network Loads

Materialized view is used to reduce network loads. Instead of accessing a single database user load is distributed across multiple database servers. Through the use of multi-tier materialized views, you can create materialized views based on other materialized views, which enables you to distribute user load to an even greater extent because clients can access materialized view sites instead of master sites. To decrease the amount of data that is replicated, a materialized view can be a subset of a master table or master materialized view.

Create a Mass Deployment Environment

Deployment templates allow you to pre-create a materialized view environment locally and then you can use deployment templates to quickly and easily deploy materialized view environments to support mass deployment environments. This technology allows you to roll out a database infrastructure to hundreds and thousands of users.

Enable Data Subsetting

Data subsetting enables you to replicate information that based on column and row level subsetting pertains only to a particular site. For example, if you have a regional sales office, then you might replicate only the data that is needed in that region, thereby cutting down on unnecessary network traffic.

Enable Disconnected Computing

Materialized views do not require a dedicated network connection as it has the option of automating the refresh process by scheduling a job; you can also manually refresh your materialized view on-demand, which is an ideal solution for sales applications running on a laptop. For example, a developer can integrate the replication management API for refresh on-demand into the sales application. When the salesperson has completed the day's orders, the salesperson simply dials up the network and uses the integrated mechanism to refresh the database, thus transferring the orders to the main office.

Nature of Materialized Views

Read-only Materialized Views
Updatable Materialized Views
Writable Materialized Views

Read-Only Materialized Views

Users cannot perform data manipulation language (DML) statements on read-only materialized views, but they can perform DML on updatable and writeable materialized views.
CREATE MATERIALIZED VIEW hr.employees AS
  SELECT * FROM HRMS.PAY_PAYMENT_MASTER@orc3.world;

Updatable Materialized Views

You can make a materialized view updatable during creation by including the FOR UPDATE clause or enabling the equivalent option in the Replication Management tool. For changes made to an updatable materialized view to be pushed back to the master during refresh, the updatable materialized view must belong to a materialized view group.
Updatable materialized views enable you to decrease the load on master sites because users can make changes to the data at the materialized view site.
CREATE MATERIALIZED VIEW hr.departments FOR UPDATE AS
SELECT * FROM HRMS.PARTIAL_PAYMENT_SEQUENCE@orc3.world;

The following statement creates a materialized view group:
BEGIN
   DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
      gname => 'HRMS_repg',
      master => 'orc3.world',
      propagation_mode => 'ASYNCHRONOUS');
END;
/
The following statement making the materialized view updatable:
BEGIN
   DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
      gname => 'HRMS_repg',
      sname => 'HRMS',
      oname => 'PARTIAL_PAYMENT_SEQUENCE',
      type => 'SNAPSHOT',
      min_communication => TRUE);
END;
/
You can also use the Replication Management tool to create a materialized view group and add a materialized view to it.
Note:
§         Do not use column aliases when you are creating an updatable materialized view. Column aliases cause an error when you attempt to add the materialized view to a materialized view group using the CREATE_MVIEW_REPOBJECT procedure.
§         An updatable materialized view based on a master table or master materialized view that has defined column default values does not automatically use the master's default values.
§         Updatable materialized views do not support the DELETE CASCADE constraint.

Writeable Materialized Views

A writeable materialized view is one that is created using the FOR UPDATE clause but is not part of a materialized view group. Users can perform DML operations on a writeable materialized view, but if you refresh the materialized view, then these changes are not pushed back to the master and the changes are lost in the materialized view itself. Writeable materialized views are typically allowed wherever fast-refreshable read-only materialized views are allowed. The Available Materialized views are: Primary Key Materialized Views, Object Materialized Views, ROWID Materialized Views, Complex Materialized Views

Primary Key Materialized Views

Primary key materialized views are the default type of materialized view. Here Changes are propagated according to the row-level changes that have occurred, as identified by the primary key value of the row (not the ROWID).
CREATE MATERIALIZED VIEW ps.Pay_Payment_Master FOR UPDATE AS
SELECT * FROM ps.Pay_payment_master@orc3.world;

Object Materialized Views

An object materialized view is composed of row objects, and each row object is identified by an object identifier (OID) column.
CREATE MATERIALIZED VIEW Payslip_date OF category_typ
REFRESH FAST FOR UPDATE AS SELECT * FROM Pay_Payment_Master@orc3.world;
Note: The types must be exactly the same at the materialized view site and master site

ROWID Materialized Views

For backward compatibility, Oracle supports ROWID materialized views in addition to the default primary key materialized views. A ROWID materialized view is based on the physical row identifiers (rowids) of the rows in a master. ROWID materialized views should be used only for materialized views based on master tables from an Oracle version 7 database, and should not be used when creating new materialized views based on masters from Oracle 8 or higher databases.
CREATE MATERIALIZED VIEW ps.pay_payment_master REFRESH WITH ROWID AS
SELECT * FROM ps.pay_payment_master@orc3.world;

Complex Materialized Views

To be fast refreshed, the defining query for a materialized view must observe certain restrictions. If you require a materialized view whose defining query is more general and cannot observe the restrictions, then the materialized view is complex and cannot be fast refreshed. Specifically, a materialized view is considered complex when the defining query of the materialized view contains:
§               A CONNECT BY clause
§               An INTERSECT, MINUS, or UNION ALL set operation
§               In some cases, the DISTINCT or UNIQUE keyword
§               An aggregate function
§               Joins other than those in a subquery
§               Clauses that do not comply with the requirements detailed in "Restrictions for Materialized Views with Subqueries".

CREATE MATERIALIZED VIEW hrms.pay_employee_personal_info AS
SELECT LPAD(' ', 4*(LATIN_NAME-10))||user_id LATIN_NAME
FROM hrms.pay_employee_personal_info@orc3.world START WITH BIRTH_DATE IS NULL
CONNECT BY PRIOR EMPLOYEE_NUMBER = COMPANY_NUMBER;

Required Privileges for Materialized View Operations

CREATE MATERIALIZED VIEW or CREATE ANY MATERIALIZED VIEW
CREATE TABLE or CREATE ANY TABLE
SELECT object privilege on the master and the master's materialized view log or SELECT ANY TABLE system privilege.

Materialized Views with Subqueries

The previous example works well for individual materialized views that do not have any referential constraints to other materialized views. But, if you want to replicate data based on the information in more than one table, then maintaining and defining these materialized views may be difficult.
Many to One Subqueries
CREATE MATERIALIZED VIEW ps.pay_payment_master REFRESH FAST FOR UPDATE AS
SELECT * FROM ps.pay_payment_master@orc3.world o
WHERE EXISTS (SELECT * FROM ps.payroll_main_file@orc3.world c
WHERE o.employee_number = c.employee_number AND c.payslip_number=o.payslip_number;
One to Many Subqueries
CREATE MATERIALIZED VIEW ps.payroll_main_file REFRESH FAST FOR UPDATE AS
SELECT * FROM ps.payroll_main_file@orc3.world c
 WHERE EXISTS(SELECT * FROM ps.pay_payment_master@orc3.world o
WHERE o.employee_number = c.employee_number AND c.payslip_number=o.payslip_number;
Many to Many Subqueries
CREATE MATERIALIZED VIEW lo.pay_loan_history REFRESH FAST FOR UPDATE AS
SELECT * FROM lo.pay_loan_history@orc3.world l
WHERE l.loan_amount < sattled_amount
(SELECT * FROM lo.pay_loan_trans@orc3.world o
WHERE l.loan_code = o.loan_cod);
Materialized Views with Subqueries and Unions
CREATE MATERIALIZED VIEW oe.product_information REFRESH FAST FOR UPDATE AS
SELECT * FROM product_information@orc3.world pi
WHERE pi.category_id = 29 AND EXISTS(SELECT * FROM product_descriptions@orc3.world pd
WHERE pi.product_id = pd.product_id AND translated_description LIKE   ‎‎'%Rouge%') 
UNION
SELECT * FROM product_information@orc3.world pi
WHERE pi.category_id = 29 AND EXISTS (SELECT * FROM oe.inventories@orc3.world i
WHERE pi.product_id = i.product_id AND EXISTS (SELECT * FROM oe.warehouses@orc3.world w
WHERE i.warehouse_id = w.warehouse_id AND EXISTS (SELECT * FROM hr.locations@orc3.world l
WHERE w.location_id = l.location_id AND l.state_province = 'California')));

Restrictions for Materialized Views with Subqueries

§         Materialized views must be primary key materialized views.
§         The master's materialized view log must include certain columns referenced in the subquery. For information about which columns must be included, see "Logging Columns in the Materialized View Log".
§         If the subquery is many to many or one to many, join columns that are not part of a primary key must be included in the materialized view log of the master. This restriction does not apply to many to one subqueries.
§         The subquery must be a positive subquery. For example, you can use EXISTS, but not NOT EXISTS.
§         The subquery must use EXISTS to connect each nested level (IN is not allowed).
§         Each table can be in only one EXISTS expression.
§         The join expression must use exact match or equality comparisons.
§         Each table can be joined only once within the subquery.
§         A primary key must exist for each table at each nested level.
§         Each nested level can only reference the table in the level above it.
§         Subqueries can include AND operators, but each OR operator may only reference columns contained within one row. Multiple OR operators within a subquery can be connected with an AND operator.
§         All tables referenced in a subquery must reside in the same master site or master materialized view site.

Restrictions for Materialized Views with Unions Containing Subqueries

§         All of the restrictions described in the previous section, "Restrictions for Materialized Views with Subqueries", apply to the subqueries in each union block.
§         All join columns must be included in the materialized view log of the master, even if the subquery is many to one.
§         All of the restrictions described in the previous section, "Complex Materialized Views", for clauses with UNIONS.

Determining the Fast Refresh Capabilities of a Materialized View

To determine whether a materialized view's subquery satisfies the restrictions detailed in the previous section, create the materialized view with fast refresh. Oracle returns errors if the materialized view violates any restrictions for subquery materialized views. If you specify force refresh, then you may not receive any errors because, when a force refresh is requested, Oracle automatically performs a complete refresh if it cannot perform a fast refresh.
You can also use the EXPLAIN_MVIEW procedure in the DBMS_MVIEW package to determine the following information about an existing materialized view or a proposed materialized view that does not yet exist:
§               The capabilities of a materialized view
§               Whether each capability is possible
§               If a capability is not possible, why it is not possible
This information can be stored in a varray or in the MV_CAPABILITIES_TABLE. If you want to store the information in the table, then, before you run the EXPLAIN_MVIEW procedure, you must build this table by running the utlxmv.sql script in the Oracle_home/rdbms/admin directory.For example, to determine the capabilities of the ps.pay_payment_master materialized view, enter:
EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW ('ps.pay_payment_master');
Or, if the materialized view does not yet exist, then you can supply the query that you want to use to create it.

0 comments:

Post a Comment