Oracle MV materialized view entry __oracle

The materialized view concept is similar to the summary table in discoverer. At the discoverer management end, you can create a different summary table. In the discoverer query, discoverer first parse the query to determine whether the query can use the corresponding summary table, if you can, will rewrite the query to query the corresponding summary table.

An example

With the following example, the use of materialized views will have a faster access rate when compared to statistical data requirements.

--Create a large table
Sql> CREATE TABLE My_all_objects
2 nologging
3 AS
4 SELECT * FROM All_objects
5 UNION ALL
6 SELECT * FROM All_objects
7 UNION ALL
8 SELECT * FROM All_objects
9/
Table created.

sql> Insert/*+ APPEND/into My_all_objects
2 Select * from My_all_objects;
87945 rows created.

Sql> commit;
Commit complete.

sql> Insert/*+ APPEND/into My_all_objects
2 Select * from My_all_objects;
175890 rows created.

Sql> commit;
Commit complete.

sql> Analyze table my_all_objects compute statistics;
Table analyzed.

Sql> Set Autotrace traceonly

--by executing the plan you can learn to count directly on the data and need access to 4800 blocks of data
Sql> Select owner, COUNT (*) from My_all_objects GROUP by owner;
Rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT optimizer=choose (cost=989 card=28 bytes=14
0)
1 0 SORT (GROUP by) (cost=989 card=28 bytes=140)
2 1 TABLE ACCESS (full) ' my_all_objects ' (cost=471 card=3
51780 bytes=1758900)

Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
4804 consistent gets
3004 Physical Reads
0 Redo Size
973 Bytes sent via sql*net to client
510 bytes received via sql*net from client
3 sql*net roundtrips To/from Client
1 Sorts (memory)
0 Sorts (disk)
Rows processed

Sql> set Autotrace off

--Give the test user query rewrite permissions
Sql> Grant query rewrite to Scott;
Grant succeeded.

--Change the current session to query rewrite
Sql> alter session set Query_rewrite_enabled=true;
Session altered.

--Set query_rewrite_integrity value of enforced, there are three optional, enforced,trusted,stale_tolerated
Sql> alter session set query_rewrite_integrity=enforced;
Session altered.

--Creating materialized views
Sql> Create materialized View My_all_objects_aggs
2 Build Immediate
3 Refresh on Commit
4 Enable query rewrite
5 as
6 Select Owner, COUNT (*)
7 from My_all_objects
8 GROUP By Owner
9/
Materialized view created.

--Analyzing materialized views
sql> Analyze table My_all_objects_aggs compute statistics;
Table analyzed.

--by executing the plan, you can learn that the same count calculation is required to access only 5 blocks of data. At the same time, you can see through the execution path that the query is done through materialized views. Using materialized views will save more resources if the business has more frequent calculations for this count. is a way of exchanging time in space.
Sql> Set Autotrace traceonly
Sql> Select owner, COUNT (*)
2 from My_all_objects
3 Group by Owner;
Rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT optimizer=choose (cost=2 card=28 bytes=252)
1 0 TABLE ACCESS (full) ' My_all_objects_aggs ' (cost=2 card= bytes=252)

Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
5 Consistent gets
0 physical Reads
0 Redo Size
973 Bytes sent via sql*net to client
510 bytes received via sql*net from client
3 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
Rows processed

Sql> set Autotrace off

--Insert a new record
sql> INSERT INTO My_all_objects
2 (owner, object_name, Object_type, object_id)
3 values
4 (' New Owner ', ' New Name ', ' New Type ', 1111111);
1 row created.

Sql> commit;
Commit complete.

Sql> Set Timing on

--The count calculation for new records is still accessed through materialized visual graphs. Because of the creation of materialized views, the table's new record has been refreshed to materialized views using the "refresh on commit" statement.
Sql> Select owner, COUNT (*)
2 from My_all_objects
3 Where owner = ' New owner '
4 Group By Owner;

OWNER COUNT (*)
------------------------------ ----------
New Owner 1

elapsed:00:00:00.00
Sql> Set Timing off
Sql>
Sql> Set Autotrace traceonly
Sql> Select owner, COUNT (*)
2 from My_all_objects
3 Where owner = ' New owner '
4 Group By Owner;


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT optimizer=choose (cost=2 card=1 bytes=9)
1 0 TABLE ACCESS (full) ' My_all_objects_aggs ' (cost=2 card= 1 bytes=9)

Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
4 consistent gets
0 physical Reads
0 Redo Size
442 bytes sent via sql*net to client
499 bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed

Sql> set Autotrace off

--Change SQL, do not query owner field, count only, find Oracle smart enough, even if no group statement is created for materialized views, or from materialized view
Sql> Set Autotrace traceonly
Sql> Select COUNT (*)
2 from My_all_objects
3 Where owner = ' New owner ';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT optimizer=choose (cost=2 card=1 bytes=9)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (full) ' My_all_objects_aggs ' (cost=2 card=1 bytes=9)





Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
3 Consistent gets
0 physical Reads
0 Redo Size
379 Bytes sent via sql*net to client
499 bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed

Sql> set Autotrace off

For OLTP systems where things are frequent, use materialized views as little as possible.

Setting Parameters

The following parameters can be set at the database or session level query_rewrite_enabled and
Query_rewrite_integrity value. For query_rewrite_integrity there are three values to set:
Enforced: Overrides only the constraints and relationships defined in the database.
Trusted: In addition to the constraints and relationships defined in the database, Oracle also uses some of the other relationships we have told Oracle tables, allowing the database to rewrite more queries.
Stale_tolerated: The weakest parameter, the materialized view does not synchronize updates, and the materialized view is used to rewrite SQL.

Query Rewrite

full text exact match
If the query's statement matches exactly the materialized view string stored in the data dictionary, the query is overwritten. The exact match here is friendlier relative to the shared pool, ignoring spaces, capitalization, and other formats.

Partial text matching
Compares the text after the from factor, even if the select part does not match. For example:
Next to the example, the materialized view query part of the SQL:
Select owner, COUNT (*) from My_all_objects GROUP by Owner
The following query can be overridden:
Select Lower (owner) from My_all_objects Group by Owner

General rewrite methods
A. Data satisfaction: Query data columns in materialized view query column
B. Connection compatibility: The associated columns in a query statement need to be in a query column in a materialized view
C. Grouping compatibility: Both the query statement and the materialized view must have a GROUP by statement, while the group by grouping level of the materialized view should be higher or equal to the statement of the query.
D. Aggregation compatibility: Both the query statement and the materialized view must contain a clustered statement. If the materialized view contains the sum ()/count () function, calculations can be overridden for the same column using the age () function.

Make sure you use materialized views

The example below will show you how to ensure that materialized views are rewritten in the context of the query and compare the difference between query_rewrite_integrity value enforced and trusted.

-This section of the statement verifies that if the constraint is related in the database, the constraint is defined in the materialized view
--use. So, if the query satisfies other overridden conditions (data satisfaction, association compatibility,
--group compatibility, etc.), and will not be overridden.

--Create test tables and materialized views
Sql> CREATE TABLE EMP as SELECT * from Scott.emp;
Table created.

Sql> CREATE TABLE Dept as select * from Scott.dept;
Table created.

Sql> alter session set Query_rewrite_enabled=true;
Session altered.

-Note that the parameter value used here is enforced, and only the constraints and relationships in the query exist in the materialized view to rewrite the query.
Sql> alter session set query_rewrite_integrity=enforced;
Session altered.

--Create materialized views, note that this is "refresh on demand" and require manual refresh of materialized views
Sql> Create materialized View emp_dept
2 Build Immediate
3 Refresh on Demand
4 Enable query rewrite
5 as
6 Select Dept.deptno, Dept.dname, COUNT (*)
7 from EMP, dept
8 Where Emp.deptno = Dept.deptno
9 GROUP by Dept.deptno, Dept.dname
10/
Materialized view created.

Sql> alter session set Optimizer_goal=all_rows;
Session altered.

Sql> set Autotrace on

--by executing the plan, you can see that the query is not rewritten, but instead accesses the table "EMP" directly. This is because we do not define the relationship between the main outer health of the table EMP and the dept, which is used in materialized views, such as "Emp.deptno = Dept.deptno"
Sql> Select COUNT (*) from EMP;

COUNT (*)
----------
14

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT optimizer=all_rows (cost=2 card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (full) ' EMP ' (cost=2 card=82)

Statistics
----------------------------------------------------------
... slightly

-This section of the statement verifies that the query is overridden after adding the related constraint.

--to increase related constraints and relationships
sql> ALTER TABLE Dept
2 Add constraint DEPT_PK primary key (DEPTNO);
Table altered.

sql> ALTER TABLE EMP
2 Add Constraint Emp_fk_dept
3 foreign KEY (DEPTNO) References dept (DEPTNO);
Table altered.

Sql> ALTER TABLE EMP modify DEPTNO NOT NULL;
Table altered.

Sql> set Autotrace on

-Because of the increased constraints, Oracle is able to rewrite the query to access the data through the materialized view "Emp_dept". You can view the execution plan for the highlight section below.
Sql> Select COUNT (*) from EMP;

COUNT (*)
----------
14

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT optimizer=all_rows (cost=2 card=1 bytes=13)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (full) ' emp_dept ' (cost=2 card=82 bytes=
1066)

Statistics
----------------------------------------------------------
Slightly....

Sql> set Autotrace off


--The following section of SQL will be in the comparison query_rewrite_integrity in the value of enforced and
--trusted the case is overridden.

--Delete the constraint established by the above steps
sql> ALTER TABLE EMP drop constraint emp_fk_dept;
Table altered.

sql> ALTER TABLE dept drop constraint DEPT_PK;
Table altered.

Sql> ALTER TABLE EMP modify DEPTNO null;
Table altered.

--Inserting data
sql> INSERT INTO EMP (EMPNO,DEPTNO) VALUES (1, 1);
1 row created.

--Manually refreshing materialized views because materialized views are created using the "Refresh on Demand"
sql> exec Dbms_mview.refresh (' emp_dept ');
Pl/sql procedure successfully completed.

--Increase the constraint again, but this time adds the novalidate factor. This can be successfully created even if the data does not conform to constraints.
sql> ALTER TABLE Dept
2 Add constraint DEPT_PK primary key (DEPTNO)
3 rely Enable Novalidate
4/
Table altered.

sql> ALTER TABLE EMP
2 Add Constraint Emp_fk_dept
3 foreign KEY (DEPTNO) References Dept (DEPTNO)
4 rely Enable Novalidate
5/
Table altered.

Sql> ALTER TABLE EMP Modify DEPTNO not NULL novalidate;
Table altered.

Sql> set Autotrace on

--Set query_rewrite_integrity value to enforced
Sql> alter session set query_rewrite_integrity=enforced;
Session altered.

--If the value of Query_rewrite_integrity is enforcedenforced, the materialized view will not be exploited to rewrite the query if the data is not satisfied with the constraint.
Sql> Select COUNT (*) from EMP;
COUNT (*)
----------
15

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT optimizer=all_rows (cost=2 card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (full) ' EMP ' (cost=2 card=164)

Statistics
----------------------------------------------------------
Slightly....

--Set query_rewrite_integrity value to trusted
Sql> alter session set query_rewrite_integrity=trusted;
Session altered.

--If the value of query_rewrite_integrity is trusted, the materialized view will be exploited to rewrite the query if the data is not satisfied with the constraint.
Sql> Select COUNT (*) from EMP;
COUNT (*)
----------
14

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT optimizer=all_rows (cost=2 card=1 bytes=13)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (full) ' emp_dept ' (cost=2 card=82 bytes=
1066)

Statistics
----------------------------------------------------------
Slightly....

Dimension
Create a latitude map a parent-child relationship between different columns, similar to the dimension created in discoverer, which allows you to drill up on demand at the time of the query. Here you can provide more information to Oracle, making it possible to rewrite the query. Very similar to the concept of hierarchy in discoverer.

Create dimension Sales_dimension
--This section defines an alias similar to a database field
Level cust_id is customer_hierarchy.cust_id
Level Zip_Code is Customer_hierarchy.zip_code
Level region is Customer_hierarchy.region
Level ' is Time_hierarchy.day
Level mmyyyy is time_hierarchy.mmyyyy
Level qtr_yyyy is time_hierarchy.qtr_yyyy
Level yyyy is TIME_HIERARCHY.YYYY
--Define one of the hierarchies
Hierarchy Cust_rollup
(
cust_id Child of
Zip_Code Child of
Region
)
--Define another hierarchy
Hierarchy Time_rollup
(
Day, child of
MMYYYY Child of
QTR_YYYY Child of
yyyy
)
--mmyyyy and mon_yyyy are synonyms.
Attribute mmyyyy
Determines mon_yyyy;

Dbms_olap
Through Dbms_olap, you can do the following:
A. Estimating the size of materialized views
B. Verifying the validity of a dimension object
C. It is recommended that you establish a materialized view to identify the views that need to be deleted and rename them
D. Assessing the use of materialized views

Reproduced from: http://itjaj.com/thread-2277-1-6.html