DBMS_MVIEW
DBMS_MVIEW enables
you to understand capabilities for materialized views and potential
materialized views, including their rewrite availability. It also enables you
to refresh materialized views that are not part of the same refresh group and
purge logs.
ESTIMATE_MVIEW_SIZE Procedure
This procedure estimates the size of a materialized view that you might
create, in bytes and number of rows.DBMS_MVIEW.ESTIMATE_MVIEW_SIZE (
stmt_id IN VARCHAR2,
select_clause IN VARCHAR2,
num_rows OUT NUMBER,
num_bytes OUT NUMBER);
EXPLAIN_MVIEW Procedure
This procedure enables you to learn what is possible with a materialized
view or potential materialized view. For example, you can determine if a
materialized view is fast refreshable and what types of query rewrite you can
perform with a particular materialized view.
DBMS_MVIEW.EXPLAIN_MVIEW
(
mv
IN VARCHAR2,
statement_id
IN VARCHAR2:= NULL);
EXPLAIN_REWRITE Procedure
This procedure enables you to learn why a query failed to rewrite, or, if it
rewrites, which materialized views will be used. Using the results from the
procedure, you can take the appropriate action needed to make a query rewrite
if at all possible.
Syntax
You can obtain the output from DBMS_MVIEW.EXPLAIN_REWRITE in two ways. The first is to use a table, while the second
is to create a VARRAY.
The following shows the basic syntax for using an output table:
DBMS_MVIEW.EXPLAIN_REWRITE
(
query VARCHAR2,
mv VARCHAR2(30),-- in the
form of
schema.mv
statement_id VARCHAR2(30));
You can create an output table
called REWRITE_TABLE by executing the utlxrw.sql script.
DBMS_MVIEW.EXPLAIN_REWRITE(query_txt,
'mv1, mv2, mv3')
REFRESH Procedures
This procedure refreshes a list of materialized views.
Syntax
DBMS_MVIEW.REFRESH (
{ list IN VARCHAR2,
| tab IN DBMS_UTILITY.UNCL_ARRAY,}
method IN VARCHAR2 := NULL,
rollback_seg IN VARCHAR2 := NULL,
push_deferred_rpc IN BOOLEAN := true,
refresh_after_errors IN BOOLEAN := false,
purge_option IN BINARY_INTEGER := 1,
parallelism IN BINARY_INTEGER := 0,
heap_size IN BINARY_INTEGER := 0,
atomic_refresh IN BOOLEAN := true,
nested IN BOOLEAN := false,
out_of_place IN BOOLEAN := false);
REFRESH_ALL_MVIEWS Procedure
This procedure refreshes all materialized views that have the following
properties:
· The
materialized view has not been refreshed since the most recent change to a
master table or master materialized view on which it depends.
· The
materialized view and all of the master tables or master materialized views on
which it depends are local.
· The
materialized view is in the view
This procedure is intended for use with data warehouses.DBA_MVIEWS.
Syntax
DBMS_MVIEW.REFRESH_ALL_MVIEWS (
number_of_failures OUT BINARY_INTEGER,
method IN VARCHAR2 := NULL,
rollback_seg IN VARCHAR2 := NULL,
refresh_after_errors IN BOOLEAN := false,
atomic_refresh IN BOOLEAN := true,
out_of_place IN BOOLEAN := false);
REFRESH_DEPENDENT Procedures
This procedure refreshes all materialized views that have the following
properties:
· The
materialized view depends on a master table or master materialized view in the
list of specified masters.
· The
materialized view has not been refreshed since the most recent change to a
master table or master materialized view on which it depends.
· The
materialized view and all of the master tables or master materialized views on
which it depends are local.
· The
materialized view is in the view
This procedure is intended for use with data warehouses.DBA_MVIEWS.
Syntax
DBMS_MVIEW.REFRESH_DEPENDENT (
number_of_failures OUT BINARY_INTEGER,
{ list IN VARCHAR2,
| tab IN DBMS_UTILITY.UNCL_ARRAY,}
method IN VARCHAR2 := NULL,
rollback_seg IN VARCHAR2 := NULL,
refresh_after_errors IN BOOLEAN := false,
atomic_refresh IN BOOLEAN := true,
nested IN BOOLEAN := false,
out_of_place IN BOOLEAN := false);
No comments:
Post a Comment