Features:-
Ø A view is a
result of a stored query.
Ø We can also say
it’s a logical representation of a table.
Ø When you do any
modification on base tables automatically it will get effected on views.
Ø There could be
performance issues in views because when you execute views it also run
underline query.
Creating a Normal View:-
SQL> Create view view-name as
select * from table-name;
v Before creating materialized view
pls find some of its Features and Advantages of Materialized views.
Materialized
VIEWS
Ø Materialized
views are database object which contains results of a query. Database stored
in a separate object. It will not
affect immediate because it depends upon refresh parameters.
Ø Performance wise
it is more robust if we compare with views because when you execute materialize-views
it will not execute base query whereas in view it executes base query.
Ø We can create
separate indexes on materialized views.
Ø Traditionally,
data warehousing and other similar large databases have needed summary tables
to perform their work. Defining these summary tables and constantly maintaining
them was a complex task. Any time you added data to the underlying detail
table, you had to manually update all the summary tables and their indexes.
Ø When
you create a new materialized view, Oracle will automatically create an
internal table to
hold the data of this materialized view. Thus, a materialized
view will take
up physical space in
your database.
Refreshing
Materialized View Data
Ø Since
a materialized view is defined on underlying master tables, when the data in
the master tables changes, the materialized view becomes outdated. To take care
of this problem, materialized views are updated, thus keeping them in sync with
the master tables. The following sections present the materialized view refresh
options.
Refresh
Modes:-
You can choose between the ON COMMIT
and ON DEMAND
modes
of data refresh.
• ON COMMIT: In this mode,
whenever a data change in one of the master tables is committed, the
materialized view is refreshed automatically to reflect the change.
• ON DEMAND: In this mode,
you must execute a procedure like DBMS_MVIEW.REFRESH to update the
materialized view.
Refresh
Types:-
You can choose from the following four refresh types:
Ø
COMPLETE: This refresh
option will completely recalculate the query underlying the materialized view.
Thus, if the materialized view originally took you 12 hours to build, it’ll
take about the same time to rebuild it. Obviously, you wouldn’t want to use
this option each time a few rows are modified, dropped, or inserted into your
master tables.
Ø
FAST REFRESH: Under the fast
refresh mechanism, Oracle will use a materialized view log to log all changes
to the master tables. It’ll then use the materialized view log to update the
materialized view. The materialized view log is a table based on the associated
materialized view. Each of the tables involved in the join in the materialized
view needs its own materialized view log to capture changes to the tables.
Ø
FORCE: If you choose
this option, Oracle will try to use the fast refresh mechanism. If it isn’t
able to use it for some reason, it’ll use the complete refresh method.
Ø NEVER: This refresh
option never refreshes a materialized view. Obviously, this isn’t a viable
option for a materialized view whose master tables undergo significant change
over time.
Ø The default refresh type is FORCE.
Query
Rewrite
Ø If
users write queries using the underlying table, Oracle will automatically
rewrite those queries
to use the materialized views this query-optimization
technique is known as query rewrite.
Ø By default query_rewrite_enabled
parameter is True.
Ø In
large databases with heavy load- and processing power–consuming activity, such
as table
joins and the use of aggregates like SUM, materialized
views speed up queries.
Ø Materialized
views makes queries faster by recalculating and storing the results of
expensive join and aggregate operations.
Ø The
beauty of Oracle’s materialized view facility is that you can specify during
their creation that the database must automatically update the materialized
views whenever there are changes in the underlying base tables.
Ø The
Oracle CBO will automatically recognize that it should rewrite a user’s query
to use the materialized view rather than the underlying tables if the estimated
query cost of using the materialized views is lower. Query cost here refers to
the I/O, CPU, and memory costs involved processing a SQL query. Complex
joins involve a lot of I/O and CPU expense, and the use of materialized views
will avoid incurring this cost each time you need to perform such joins.
Because the materialized views already have the summary information precomputed
in them, your
queries will cost much less in terms of resource usage, and hence
run much more quickly.
Ø The
QUERY_REWRITE_ENABLED
initialization
parameter allows you to enable or disable query
writing at a global level. The
parameter can take the following values:
• FALSE: The database
doesn’t rewrite any queries.
• TRUE: The database
compares the cost of the query with and without a rewrite and
Chooses the
cheaper method.
• FORCE: The database
always rewrites the query, without evaluating any costs.
Use the FORCE setting
if you are certain that the query is beneficial and will result in
shortening the
response time.
Ø The
default value for this parameter is TRUE, provided you set the OPTIMIZER_FEATURES_ENABLE
parameter
to 10.0.0 or higher (it is FALSE if you set the OPTIMIZER_FEATURES_ENABLE parameter to 9.2.0
or lower), which means that Oracle automatically uses the query rewrite
feature. When the parameter is set to TRUE, Oracle will estimate the cost of
the query both with and without a rewrite
and will choose the one with the
lesser processing cost. When you enable query
rewriting, it’s enabled
systemwide, for the entire database.
Ø You
must specify the FORCE value for the OPTIMIZER_QUERY_REWRITE parameter only if you are sure that
it is beneficial to do so. To enable query rewriting for a specific
materialized view, you
must explicitly specify the ENABLE
QUERY REWRITE clause
when you create the materialized
view.
Steps:-
Ø Target database
Table should have primary (For refresh Parameters).
SQL> alter table emp add constraint pk_emp primary key (empno,ename);
Table altered.
Ø Create materialized view log.
SQL> create materialized view log on fmis.emp; ()
Materialized view log created.
SQL> create materialized view eyes_mv1
refresh
fast
START WITH SYSDATE NEXT SYSDATE + 1
Enable query rewrite
as select *
from emp@mmmlink;
Materialized view created.
Querying materialized views
SQL> select * from emp@mmmlink;
SQL> SELECT owner, mview_name, last_refresh_date FROM
all_mviews;
OWNER
MVIEW_NAME
LAST_REFR
--------- ------------------------------
--------------------------------------------------------------------------------------------------
SYSMAN
MGMT_ECM_MD_ALL_TBL_COLUMNS 03-NOV-11
SYS
EYES_MV
07-JUN-15
SYS
EYES_MV1
07-JUN-15
SYS
EYES_MV2
07-JUN-15
SQL> SELECT mview_name, last_refresh_date,
fullrefreshtim, increfreshtim FROM dba_mview_analysis;
MVIEW_NAME
LAST_REFR
FULLREFRESHTIM INCREFRESHTIM
----------------------------- --------- --------------
------------------------------------------------------------------------------------
MGMT_ECM_MD_ALL_TBL_COLUMNS 03-NOV-11 1 0
EYES_MV
07-JUN-15 0 0
EYES_MV1
07-JUN-15 0 0
EYES_MV2 07-JUN-15 0 0
SQL> select MVIEW_NAME,
to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mviews;
MVIEW_NAME
TO_CHAR(LAST_REFRES
------------------------------
----------------------------------------------------------------------------
MGMT_ECM_MD_ALL_TBL_COLUMNS 2011-11-03 06:20:15
EYES_MV 2015-06-07
13:57:48
EYES_MV1
2015-06-07 15:00:31
EYES_MV2
2015-06-07 15:19:12
You can go for manual refresh also by executing DMS procedure
SQL> EXEC DBMS_MVIEW.REFRESH('EYES_MV1');
PL/SQL procedure successfully completed.
SQL> select NAME, to_char(LAST_REFRESH,'YYYY-MM-DD
HH24:MI:SS') from dba_mview_refresh_times;
NAME
TO_CHAR(LAST_REFRES
------------------------------
---------------------------------------------------------------------
EYES_MV 2015-06-08
11:07:57
EYES_MV1
2015-06-08 11:07:47
EYES_MV2
2015-06-08 12:36:27
MGMT_ECM_MD_ALL_TBL_COLUMNS 2011-11-03 06:20:15
Happy Learning ...
No comments:
Post a Comment