SQL - Materialized View in Oracle.
Sql Access Advisor (a GUI tool for materialized view and index management) can recommend the creation of materialized views.
The end user queries the tables and views in the database. The query rewrite mechanism in the Oracle server automatically rewrites the SQL query to use the summary tables. This mechanism reduces response time for returning results from the query and his known as Database Oracle - Query Rewrite. Materialized views within the data warehouse are transparent to the end user or to the database application.
Normally, when QUERY REWRITE ENABLED is set to FALSE, Oracle will take your SQL as is, parse it, and optimize it. With query rewrites enabled, Oracle will insert an extra step into this process. After parsing, Oracle will attempt to rewrite the query to access some materialized view, instead of the actual table that it references. See for more information, the Database Oracle - Query Rewrite page.
One of the advantages of using materialized views is that there are a number of refresh mechanisms available within the Oracle Database for refreshing them as the data changes in the base tables:
When a complete refresh is performed the materialized view is fully rebuilt by re-executing its defining query: depending upon the size of the materialized view this could potentially be a costly operation.
By tracking the changes to the base data using a materialized view log, a fast refresh is able to apply only these changes to the materialized view.
Alternatively, a fast refresh can be performed by transparently detecting when changes to the data in partitions of the base tables occurs and then only the contents of those partitions need be re-computed for refreshing the materialized view.
Similarly, during direct path load operations, such as SQL*Loader direct path loads, the database automatically tracks the new data that is loaded at the block level.
These two techniques do not require a materialized view log for fast refreshes. It should be noted, however, that not all materialized views are fast refreshable and this can be identified by using the packaged procedure DBMS_MVIEW.EXPLAIN_MVIEW.
Materialized views are refreshed either on demand or on-commit. An on-demand, fast refresh is illustrated below using the DBMS_MVIEW package.
DBMS_MVIEW.REFRESH( list => ’state_sales_mv, channels_count_mv’, method => ’F’);
Having a fact table that is partitioned offers two additional benefits for materialized views. The fact that only some partitions have changed, due to a DML or a partition maintenance operation, is useful for:
More Partitioning and Materialized Views…
Package :
DBMS_SNAPSHOT is a synonym for DBMS_MVIEW.
There is one mandatory INIT.ORA parameter necessary for materialized views to function, this is the COMPATIBLE parameter. The value of COMPATIBLE should be set to 8.1.0, or above, in order for query rewrites to be functional.
The needed privileges are as follows:
Finally, you must be using the Cost Based Optimizer CBO in order to make use of query rewrite. If you do not use the CBO, query rewrite will not take place.
grant create session to scott;
grant create table to scott;
grant create materialized view to scott;
DROP MATERIALIZED VIEW cust_sales_mv ;
CREATE MATERIALIZED VIEW cust_sales_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT c.cust_id,
SUM(amount_sold) AS dollar_sales,
COUNT(amount_sold) AS cnt_dollars,
COUNT(*) AS cnt
FROM sales s, customers c
WHERE s.cust_id= c.cust_id
GROUP BY c.cust_id;
FORCE Clause : Specify FORCE to indicate that when a refresh occurs, Oracle Database will perform a fast refresh if one is possible or a complete refresh if fast refresh is not possible. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), then FORCE is the default.
FAST Clause : Specify FAST to indicate the incremental refresh method, which performs the refresh according to the changes that have occurred to the master tables. The changes for conventional DML changes are stored in the materialized view log associated with the master table. If you specify REFRESH FAST, then the CREATE statement will fail unless materialized view logs already exist for the materialized view master tables. Oracle Database creates the direct loader log automatically when a direct-path INSERT takes place. No user intervention is needed. Materialized views are not eligible for fast refresh if the defining query contains an analytic function.
Error that you can have :
*
ERROR at line 8:
ORA-23413: table "SH"."CUSTOMERS" does not have a materialized view log
By specifying REFRESH ON COMMIT in our original definition of the view, we requested that Oracle maintain synchronization between the view and the details, the summary will be maintained as well.
COMPLETE Clause : Specify COMPLETE to indicate the complete refresh method, which is implemented by executing the defining query of the materialized view. If you request a complete refresh, then Oracle Database performs a complete refresh even if a fast refresh is possible.
Very often only a subset of the information in a large fact table might be considered for more analysis. To take advantage of materialized views in such a situation, you had to create a materialized view containing all the information for the fact table. Choosing to incorporate a predicate condition in the materialized view definition allowed TEXTMATCH only rewrite capabilities.
To create a materialized view on a subset of data and compare its creation time and its size with a materialized view containing the same joins and aggregations and the complete data set without any predicates, you perform the following steps:
DROP MATERIALIZED VIEW some_cust_sales_mv;
CREATE MATERIALIZED VIEW some_cust_sales_mv
BUILD IMMEDIATE
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS
SELECT c.cust_id, sum(s.amount_sold) AS dollars, p.prod_id,
sum(s.quantity_sold) as quantity
FROM sales s , customers c, products p
WHERE c.cust_id = s.cust_id
AND s.prod_id = p.prod_id
AND c.cust_state_province IN
('Dublin','Galway','Hamburg','Istanbul')
GROUP BY c.cust_id, p.prod_id;
| Time Complete Materialized view | 2 min |
| Time Materialized View on a Subset of Data | 20 sec |
It takes longer to create the complete materialized view, because all data must be touched, joined, and aggregated. The subset materialized view is chosen over the one containing all the data, because of its lower cost.
The materialized view containing the subset of data can be used for query rewrite like any other materialized view. Those materialized view only have to pass the data containment check for being eligible for rewrite.
To correct the error above, we create the materialized view logs identified above.
@create_mv_logs1.sql
DROP MATERIALIZED VIEW LOG ON sales;
CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE
(prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold)
INCLUDING NEW VALUES ;
DROP MATERIALIZED VIEW LOG ON customers;
CREATE MATERIALIZED VIEW LOG ON customers
WITH ROWID, SEQUENCE
(cust_id,cust_first_name,cust_last_name,cust_gender,cust_year_of_birth
,cust_marital_status,cust_street_address,cust_postal_code,cust_city
,cust_state_province,country_id,cust_main_phone_number,cust_income_level
,cust_credit_limit,cust_email)
INCLUDING NEW VALUES;
Now create the materialized view. The explain_mview procedure also works with existing materialized views.
It is not uncommon in a data warehouse to have already created summary or aggregation tables, and you might not want to repeat this work by building a new materialized view. Although this solution provides the performance benefits of materialized views, it does not:
Because of these limitations, and because existing materialized views can be extremely large and expensive to rebuild, the Oracle database provides you with the capability to register those already existing summary tables as materialized views, thus circumventing all the disadvantages mentioned above. You can register a user-defined materialized view with the CREATE MATERIALIZED VIEW … ON PREBUILT TABLE statement. Once registered, the materialized view can be used for query rewrites, maintained by one of the refresh methods, or both. This functionality was available beginning with Oracl8i.
Oracle implemented this capability for its existing customer base to provide a save migration path and as a protection of investment. Migrating an existing data warehousing environment with “hand-made” summary tables and refresh procedures can take advantage of the new rewrite capabilities with a single DDL command, without affecting any existing code.
MyCompany recently migrated from Oracle8.0 to Oracle9i, and does have such manually created aggregation tables, as do more than 90% of all existing data warehousing systems. To register the existing cust_id_sales_aggr table as a materialized view, you perform the following steps:
From a SQL*Plus session logged on to the SH schema, run create_mv2.sql, or copy the following SQL statement into your SQL*Plus session:
@create_mv2.sql
DROP MATERIALIZED VIEW cust_sales_aggr ;
CREATE MATERIALIZED VIEW cust_sales_aggr
ON PREBUILT TABLE
REFRESH FORCE
ENABLE QUERY REWRITE
AS
SELECT c.cust_id,
c.cust_last_name,
c.cust_first_name,
SUM(amount_sold) AS dollar_sales,
COUNT(amount_sold) AS cnt_dollars,
COUNT(*) AS cnt
FROM sales s, customers c
WHERE s.cust_id= c.cust_id
GROUP BY c.cust_id,
c.cust_last_name,
c.cust_first_name;
This statement is fairly fast. It doesn’t touch any data at all; it simply creates the meta information of a materialized view, which tables and columns are involved, which joins, and which aggregations.
Using materialized views on prebuilt tables is not possible with the highest level of data integrity for query rewrite (query_rewrite_integrity=ENFORCED), because the system “trusts” you as the creator with respect to the data integrity. As soon as you are going to leverage Oracle’s refresh capabilities, too, the system knows about the integrity of the data. However, the first refresh will be a complete refresh in any case.
CREATE MATERIALIZED VIEW all_customers
PCTFREE 5 PCTUSED 60
TABLESPACE example
STORAGE (INITIAL 50K NEXT 50K)
USING INDEX STORAGE (INITIAL 25K NEXT 25K)
REFRESH START WITH ROUND(SYSDATE + 1) + 11/24
NEXT NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 15/24
AS SELECT * FROM sh.customers@remote
UNION
SELECT * FROM sh.customers@local;
Oracle Database automatically refreshes this materialized view tomorrow at 11:00 a.m. and subsequently every Monday at 3:00 p.m.
CREATE MATERIALIZED VIEW emp_data
PCTFREE 5 PCTUSED 60
TABLESPACE example
STORAGE (INITIAL 50K NEXT 50K)
REFRESH FAST NEXT sysdate + 7
AS SELECT * FROM employees;
The statement does not include a START WITH parameter, so Oracle Database determines the first automatic refresh time by evaluating the NEXT value using the current SYSDATE. A materialized view log was created for the employee table, so Oracle Database performs a fast refresh of the materialized view every 7 days, beginning 7 days after the materialized view is created.