Example
CREATE MATERIALIZED VIEW view_name
TABLESPACE tsname
PARALLEL degree n
BUILD (immediate or deferred)
REFRESH (fast/complete/never/force/on commit)
ENABLE /DISABLE QUERY REWRITE
AS
SELECT ...... FROM
WHERE ....
GROUP BY
HAVING
ETC
init.ora
optimizer_mode = all_rows, or first _rows, first_rows_N (must be cost)
query_rewrite_enabled = (t/f)
query_rewrite_integrity = (enforced/trusted/stale_tolerated)
It is only the optimizer that determines whether to use the mview or not.
No direct access by users is allowed. That is why you must enable or
disable query rewrite either at view creation or as a hint in the select
statement that will be issued allowing the optimizer to use the
precomputed results of the view. You can also use the alter materialized
view statement to add the enable query rewrite clause to an mview after
creation. To know if the optimizer used the view or processed the select
statement anew, you acn use the explain plan for Select ... and you should
see that the table access is FULL for the mview name. You need the query
rewrite privilege to enable/disable an mview for query rewrites. Check
the DBMS_OLAP package to see mview analysis and functions.
Sabina.
"BRoussel" <oracledba-ezmlmshield-x17342744.[Email address protected]
10/31/2005 03:10 PM
To
"LazyDBA Discussion" <[Email address protected]
cc
Subject
Materialized Views
Hi DBAs,
I would like to use materialized views for a project that we are
thinking of, see below:
2 tables with 4.4 millions of rows each
Need to be read-only view
Run from Monday to Friday at 5:00am
It is my first time and I would like to have an example on how to
code this materialized view...
The environment is Unix Solaris and Oracle9i.
Thanks,
Bruno
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:
http://www.lazydba.com/legal.html
Oracle LazyDBA home page