You can use the DBMS_MVIEW.EXPLAIN_MVIEW procedure to learn what is possible with a materialized view or potential materialized view. In particular, this procedure enables you to determine:
Using this procedure is straightforward. You simply call DBMS_MVIEW.EXPLAIN_MVIEW, passing in as a single parameter the schema and materialized view name for an existing materialized view. Alternatively, you can specify the SELECT string for a potential materialized view or the complete CREATE MATERIALIZED VIEW statement. The materialized view or potential materialized view is then analyzed and the results are written into either a table called MV_CAPABILITIES_TABLE, which is the default, or to an array called MSG_ARRAY.
Note that you must run the utlxmv.sql script prior to calling EXPLAIN_MVIEW except when you are placing the results in MSG_ARRAY. The script is found in the admin directory. It is to create the MV_CAPABILITIES_TABLE in the current schema.
Using the DBMS_MVIEW.EXPLAIN_MVIEW Procedure
The EXPLAIN_MVIEW procedure has the following parameters:
An optional parameter.A client-supplied unique identifier to associate output rows with specific invocations of EXPLAIN_MVIEW.
The name of an existing materialized view or the query definition or the entire CREATE MATERIALIZED VIEW statement of a potential materialized view you want to analyze.
The PL/SQL varray that receives the output.
EXPLAIN_MVIEW analyzes the specified materialized view in terms of its refresh and rewrite capabilities and inserts its results (in the form of multiple rows)into MV_CAPABILITIES_TABLE or MSG_ARRAY.
The following PL/SQL declarations that are made for you in the DBMS_MVIEW package show the order and datatypes of these parameters for explaining an existing materialized view and a potential materialized view with output to a table and to a VARRAY.
Explain an existing or potential materialized view with output to MV_CAPABILITIES_TABLE:
Explain an existing or potential materialized view with outputto a VARRAY:
One of the simplest ways to use DBMS_MVIEW.EXPLAIN_MVIEW is with the MV_CAPABILITIES_TABLE, which has the following structure:
you can use the utlxmv.sql script found in the admin directory to create MV_CAPABILITIES_TABLE.
First, create the materialized view. Alternatively, you can use EXPLAIN_MVIEW on a potential materialized view using its SELECT statement or the complete CREATE MATERIALIZED VIEW statement.
Then, you invoke EXPLAIN_MVIEW with the materialized view to explain. You need to use the SEQ column in an ORDER BY clause so the rows will display in a logical order. If a capability is not possible, N will appear in the P column and an explanation in the MSGTXT column. If a capability is not possible for more than one reason, a row is displayed for each reason.
The following table lists explanations for values in the CAPABILITY_NAME column.
CAPABILITY_NAME Column Details
MV_CAPABILITIES_TABLE Column Details
The following table lists the semantics for RELATED_TEXT and RELATED_NUM columns
MV_CAPABILITIES_TABLE Column Details
Data Warehousing Related Interview Questions
|Informatica Interview Questions||Data Warehousing Interview Questions|
|Networking Interview Questions||System Administration Interview Questions|
|Hadoop Interview Questions||MYSQL DBA Interview Questions|
|Data modeling Interview Questions||Hadoop Administration Interview Questions|
|Apache Flume Interview Questions||Informatica Admin Interview Questions|
Data Warehousing Tutorial
Data Warehousing Concepts
Physical Design In Data Warehouses
Hardware And I/o Considerations In Data Warehouses
Parallelism And Partitioning In Data Warehouses
Basic Materialized Views
Advanced Materialized Views
Overview Of Extraction, Transformation, And Loading
Extraction In Data Warehouses
Transportation In Data Warehouses
Loading And Transformation
Maintaining The Data Warehouse
Change Data Capture
Schema Modeling Techniques
Sql For Aggregation In Data Warehouses
Sql For Analysis And Reporting
Sql For Modeling
Olap And Data Mining
Using Parallel Execution
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.