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