Basics
-Also known as snapshots, or summaries
-A materialized view creates a REAL table
-A materialized view can query
-tables,
-views
-other materialized views
-Underlying tables or views are called
-mater tables
-or detail tables
-You can
-create index on mv
-create mv on partitioned tables
-partition a mv
-Oracle rewrites your queries to use mv based on
-cost based optimization
-so optimizing mv is the key for Oracle to use it
-Benefits
-Beter performance
-Less physical writes
-Less writes
-Decreased CPU comsumption
-Faster response time
-Drawbacks
-Need more disk space
-Best used in systems that are
-Read only
-Read intensive
Query rewrite
-For query rewrite to work
-INIT.ORA
-COMPATIBLE should be > 8.1.0
-QUERY+REWRITE_ENABLED
-QUERY REWRITE INTEGRITY
-ENFORCED (default)
-TRUSTED
-STALE TOLERATED
-System privileges
-CREATE SESSION
-CREATE TABLE
-CREATE MATERIALIZED VIEW
-QUERY REWRITE
-Must use cost based optimizer (CBO)
-Force query rewrite
-By hint REWRITE_OR_ERROR
-Use DBMS_MVIEW.EXPLAIN_REWRITE to find rewrite details
-Example
SELECT /*+ REWRITE_OR_ERROR */
prod_id
SUM(quantity_sold) AS sum_sales_qty
FROM sales_data
GROUP BY prod_id
Refreshing mv data
-Refresh mode
-on commit
-on demand (default)
-Use DBMS_MVIEW.REFRESH
-Refresh type
-COMPLETE
-FAST
-Relies on materialized view log table
-FORCE (default)
-Try FAST first, COMPLETE otherwise
-NEVER
DBMS_MVIEW package
-EXPLAIN_MVIEW
-EXPLAIN_REWRITE
-TUNE_MVIEW
Create MV
-Grant necessary privileges
-CREATE MATERIALIZED VIEW
-QUERY REWRITE
-CREATE ANY TABLE
-SELECT ANY TABLE
-Create mv log on master tables
-CREATE MATERIALIZED VIEW LOG ON products;
-Create mv
-Build mode
-BUILD IMMEDIATE
-BUILD DEFERRED
-Collect optimizer statistics for mv
-analyze table mv_bigtab compute statistics;
-Example
SQL> CREATE MATERIALIZED VIEW test_mv
2 BUILD IMMEDIATE
3 REFRESH FAST ON COMMIT
4 ENABLE QUERY REWRITE
5 AS
6 SELECT sh.products.prod_category,
7 SUM(sh.sales.quantity_sold),
8 COUNT(sh.sales.quantity_sold), count(*)
10 FROM sh.sales, sh.products
11 WHERE sh.products.prod_id = sh.sales.prod_id
12 AND sh.products.prod_category <= 'Women'
13 AND sh.products.prod_category >= ‘Boys’
14 GROUP BY sh.products.prod_category;
Materialized view created.
Example
-Setup big table named bigtab
ALTER USER “SCOTT” ACCOUNT UNLOCK
/
set echo on
set termout off
drop table bigtab;
create table bigtab
nologging
as
select * from all_objects
union all
select * from all_objects
union all
select * from all_objects
/
insert /*+ APPEND */ into bigtabd
select * from bigtab;
commit;
insert /*+ APPEND */ into bigtab
select * from bigtab;
commit;
insert /*+ APPEND */ into bigtab
select * from bigtab;
commit;
analyze table bigtab compute statistics;
select count(*) from bigtab;
-Run query against bigtab
set autotrace on
set timing on
select owner, count(*) from bigtab group by owner;
-Create materialized view
-Run as system
GRANT CREATE ANY MATERIALIZED VIEW TO “SCOTT”;
GRANT QUERY REWRITE TO “SCOTT”;
alter session set query_rewrite_enabled=true;
alter session set query_rewrite_integrity=enforced;
create materialized view mv_bigtab
build immediate
refresh on commit
enable query rewrite
as
select owner, count(*)
from bigtab
group by owner
/
analyze table mv_bigtab compute statistics;
-Run query again bigtab again
-Oracle automatically redirects the select count(*) to materialized views
set timing on
set autotrace traceonly
select owner, count(*)
from bigtab
group by owner;
set autotrace off
set timing off
-Insert into bigtab
-Materialized view automatically synchronized again bigtab table
-REFRESH ON COMMIT
insert into bigtab
(owner, object_name, object_type, object_id)
values (‘Martin’, ‘Zahn’, ‘Akadia’, 1111111);
commit;
set timing on
set autotrace traceonly
select owner, count(*)
from bigtab
where owner = ‘Martin’
group by owner;
set autotrace off
set timing off