LOL
BRoussel wanted to see examples, but You are all quoting oracle
documentation and sintacsis :)
To BRoussel:
Create table t1
(
f1 number,
f2 varchar2(100),
f3 date
);
insert into t1 .....
...
create view v1 as
select f1, substr(f2, 1, 10)
from t1
where f3 > sysdate;
create materalized view mv1
refresh force on demand
with rowid
start with to_date('04-11-2005 05:00:00', 'dd-mm-yyyy hh24:mi:ss') next
sysdate+7
as
select * from v1;
Look at dba_jobs view. You'll see that one job appeared.
You can also refresh your snapshot with
sys.dbms_snapshot.refresh(.....)
or
sys.dbms_refresh.refresh(......)
Thats all folks.
There ara two refresh methods: complete and fast.
If you don't have materialized view log on t1 table, then fast refresh
is impossible.
Ok
create materialized view log on t1 with rowid;
It was just an example, for more please RTFM.
Regards,
Alexander Dubrovsky
Database Administrator
Renaissance Capital Consumer Finance
-----Original Message-----
From: Sabina
[mailto:oracledba-ezmlmshield-x26225018.[Email address protected]
Sent: Tuesday, November 01, 2005 5:11 PM
To: LazyDBA Discussion
Subject: Re: Materialized Views
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
--------
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