Expert one-on-one Oracle chapter 13 materialized view Reading Notes (3)

The biggest advantage of Materialized view is that it can improve performance: by pre-computing the answer to store it, it can greatly reduce the load on the machine.
Features:
Less physical reads-scanning less data
Less write-do not sort and aggregate frequently
Reduce CPU consumption-no need to aggregate data and call Functions
Significantly faster response time-query results will be returned quickly when data is queried using the materialized view (opposite to the primary table)

Materialized View will increase the demand for disk resources, that is, the hard disk space that needs to be permanently allocated to the materialized view to store data.
Materialized views are best used in read-only or intensive environments and are not used in online transaction processing systems (OLTP) environments.

How does materialized view work?
Assume that a materialized view is created and the answer to the question is displayed. However, for some reason, Oracle does not.
Why Does Oracle not know the answer when materialized views are used?
It is because oralce does not know some information, and some information that can tell it to obtain the information we want.
Oracle is just a software, and she can only process the information provided to it. The more metadata is provided, the better it is to read more information about potential ORACLE data. This information
It may be some constraints, primary keys, foreign keys, and so on.

The following describes some examples to illustrate what a materialized view must do and how the materialized view provided to it will be used more.
Materialized View settings:
System Level: Through init. ora
Session level: Use the alter session command
The parameters are as follows:
Query_rewrite_enabled -- if this parameter is set to true, query rewriting will occur. If it is set to false, query rewriting will not occur;
Query_rewrite_integrity -- this parameter controls how Oracle overwrites the query and may be set to the following three values:
Enforced-only queries that are forced and guaranteed by Oracle and whose rules can be rewritten;
However, since oralce does not impose any query relationship that can be known through oralce;
Trusted-rewrite the query by using the constraints imposed by oralce and any relationships that exist in the data rather than the relational imposed by the database;
Stale_tolerated-the materialized view can be used to override the query, even if oralce knows that the contained data is outdated.

Query_rewrite_enabled = false. Oracle analyzes and optimizes SQL statements;
Query_rewrite_enabled = true. Oracle uses the query rewriting function from the very beginning. After an SQL statement is analyzed, Oracle will perform one more step to rewrite this query to access some materialized views, instead of the real table it references.
If you can execute query rewriting, these rewrite queries will be analyzed and optimized together with the initial query. The execution cost is the lowest in the collection of materialized views discovered by the data dictionary.
The query scheme, the lowest query cost;
If the query cannot be rewritten, the query of the original analysis is optimized and runs properly;
1. query rewrite steps:
Exact body matching-> partial body matching-> General Query Rewriting Method (requiring sufficient data and connection compatibility)-> group compatibility-> aggregation compatibility

2. How to ensure that materialized views can be used?
Here, we only talk about three methods to help you use the query rewrite function of the Materialized View:
1) constraints;
2) dimension;
3) describe complex relationships-data Hierarchies;

The following are examples of the above three methods:
1) constraints:
SQL> Create Table EMP as select * from Scott. EMP;
Table created.
Elapsed: 00:00:06. 41
SQL> Create Table dept as select * from Scott. Dept;
Table created.
Elapsed: 00:00:01. 02

SQL> grant query rewrite to test;
Grant succeeded.
Elapsed: 00:00:01. 06

SQL> alter session set query_rewrite_enabled = true;
Session altered.
Elapsed: 00:00:00. 22

SQL> alter session set query_rewrite_integrity = enforced;
Session altered.
Elapsed: 00:00:00. 01

SQL> Create materialized view emp_dept
2 build immediate
3 refresh on demand
Enable query rewrite
4 5
6 select Dept. deptno, Dept. dname, count (*)
7 from EMP, Dept
8 where EMP. deptno = Dept. deptno
9 group by dept. deptno, Dept. dname;

SQL> alter session set optimizer_goal = all_rows;
Session altered.
Elapsed: 00:00:00. 05
(Note: All rows: This is what we call the cost method. When a table has statistics, it returns all rows of the table in the fastest way, improves the query throughput in general.
If no statistical information is available, the rule-based approach is adopted .)

At this time, oralce does not know the hunger relationship between the EMP table and the dept table, and does not know which column is a residence code.
SQL> set autotrace on

SQL> select count (*) from EMP;

Count (*)
----------
14

Elapsed: 00:00:03. 29

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = all_rows (cost = 2 card = 1)
1 0 sort (aggregate)
2 1 Table Access (full) of 'emp' (cost = 2 card = 327)

 

Statistics
----------------------------------------------------------
6 recursive cballs
0 dB block gets
11 consistent gets
0 physical reads
0 redo size
379 bytes sent via SQL * Net to client
503 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
2 sorts (memory)
0 sorts (Disk)
1 rows processed

Essentially, the SQL statement uses the grouping compatibility step in the query rewriting to implement the query using the materialized view, because it is easy to obtain information from the materialized view;
However, Oracle does not know the relationship between EMP and dept. Each employee in the EMP table belongs to the Department in the dept table, so Oracle does not use the materialized view.
We need to inform oralce about the relationship between Oracle EMP table and dept table:
SQL> ALTER TABLE Dept
Add constraint dept_pk primary key (deptno); 2 -- tell Oracle that the deptno field is the master code of the dept table

Table altered.

Elapsed: 00:00:03. 54
SQL> ALTER TABLE EMP -- tells Oracle EMP that the deptno field in the table is an external code, which corresponds to deptno In the dept table.
Add constraint emp_fk_dept
2 3 foreign key (deptno) References dept (deptno );

Table altered.

Elapsed 00:00:00. 81
SQL> ALTER TABLE EMP modify deptno not null; -- tells Oracle EMP that the deptno field is not empty.

Table altered.

Elapsed: 00:00:00. 24

Execute the select count (*) from EMP statement after telling Oracle the information:
SQL> set autotrace on
SQL> select count (*) from EMP;

Count (*)
----------
14

Elapsed: 00:00:01. 20

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = all_rows (cost = 2 card = 1 bytes = 13)
1 0 sort (aggregate)
2 1 Table Access (full) of 'emp_dept' (cost = 2 card = 327 bytes
= 4251)

Statistics
----------------------------------------------------------
315 recursive cballs
36 db block gets
85 consistent gets
0 physical reads
6864 redo size
379 bytes sent via SQL * Net to client
503 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
11 sorts (memory)
0 sorts (Disk)
1 rows processed

From the above execution plan, we can see that Oracle adopts a materialized view to query, and Oracle can rewrite the query;

In the above example, we need to tell the constraints between the EMP table and dept table of the Oracle database, but sometimes we don't want to spend any effort to verify the relationship between the external code,
I have already done this in the Data purification routine (what is data purification? One of the components of a data warehouse is "data extraction, data purification, data loading", which may be
The data purification mentioned in may mean that the data has been purified before the data is loaded into the database, and there will be no data integrity ).
The following is an example to simulate the process of loading data into the database,
The data has been purified first, then the constraints are discarded, the data is loaded, the materialized view is refreshed, and the constraints are added.
Start with dropping the constraint:
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.

The following is the data loading process. Assume that a piece of data is loaded:
SQL> insert into EMP (empno, deptno) values (1, 1 );

1 row created.

SQL> commit;

Commit complete.

The following shows how to refresh the materialized view of Oracle:
SQL> exec dbms_mview.refresh ('emp_dept ');

PL/SQL procedure successfully completed.

This tells Oracle about the relationship between EMP and dept tables:
SQL> ALTER TABLE Dept
Add constraint dept_pk primary key (deptno)
2 3 rely enable novalidate;

Table altered.

SQL> ALTER TABLE EMP
Add constraint emp_fk_dept
Foreign key (deptno) References dept (deptno)
Rely enable novalidate 2 3 4;

Table altered.

SQL> ALTER TABLE EMP modify deptno not null novalidate;

Table altered.

In the preceding statement, rely enable novalidate and novalidate indicate that Oracle does not need to check the loaded existing data any more, and rely tells Oracle to trust
The data integrity tells Oracle that if the EMP and dept tables are connected, each row of the EMP table will be retrieved.
In fact, Oracle is not a fact. A new data inserted into EMP does not have a corresponding row in Dept, which violates data integrity.
If you set alter session set query_rewrite_integrity = enforced;
Then:
SQL> alter session set query_rewrite_integrity = enforced;

Session altered.

Elapsed: 00:00:00. 00
SQL> select count (*) from EMP;

Count (*)
----------
15

Elapsed: 00:00:00. 40

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = all_rows (cost = 2 card = 1)
1 0 sort (aggregate)
2 1 Table Access (full) of 'emp' (cost = 2 card = 654)

Statistics
----------------------------------------------------------
288 recursive cballs
38 db block gets
77 consistent gets
0 physical reads
6752 redo size
379 bytes sent via SQL * Net to client
503 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
9 sorts (memory)
0 sorts (Disk)
1 rows processed

Analysis: When query_rewrite_integrity = enforced
And the guaranteed constraint and the query of the rule can be rewritten. Although the above constraint is created, this constraint does not exist.
The statement is not verified by the database, so the statement does not query and rewrite rely enable novalidate and novalidate.
Indicates that Oracle does not have to check the loaded existing data any more. In essence, the database has not confirmed the data, so it is not
Restrictions enforced and guaranteed by Oracle and query of rules


SQL> alter session set query_rewrite_integrity = trusted;

Session altered.

SQL> select count (*) from EMP;

Count (*)
----------
14

Elapsed: 00:00:00. 30

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = all_rows (cost = 2 card = 1 bytes = 13)
1 0 sort (aggregate)
2 1 Table Access (full) of 'emp_dept' (cost = 2 card = 82 bytes =
1066)

Statistics
----------------------------------------------------------
20 recursive cballs
1 dB block gets
17 consistent gets
1 physical reads
100 redo size
379 bytes sent via SQL * Net to client
503 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
2 sorts (memory)
0 sorts (Disk)
1 rows processed

Query_rewrite_integrity = trusted is set to tell Oracle to use the constraints imposed by oralce,
You can rewrite the query for any link that exists in the data instead of the database.
Considering that there is a non-database-imposed relationship between EMP and dept tables in the data, the database considers that
Query Rewriting. materialized views are used. Oracle considers the newly inserted data as a violation between the EMP and dept tables.
The relationship between the constraints. Therefore, the new inserted rows are not counted, the materialized view is refreshed in time, and the newly inserted rows are not inserted.
The data is added to the materialized view. Oracle recognizes that the data we tell him is not reliable and can know:
1. For large data warehouses, you can trust materialized views without having to verify the amount of data you have done yourself;
2. If you want the data, you must ensure that the data is reliable and 100% of the data is purified;

After talking for half a day, I will understand myself.