Shulou(Shulou.com)05/31 Report--

This article mainly introduces how Oracle uses materialized view query to rewrite query rewrite, which has a certain reference value, and interested friends can refer to it. I hope you can learn a lot after reading this article.

1. Concept

Container table: a physical table of automatically built and actually stored data during the establishment of a MATERIALIZED VIEW, and a table with the same name as the physical and chemical database

Base table: establish the materialized sentence as select. Table referenced in

Refresh mode

Refresh fast: the data in the container table will be reused. Only the modified data in the base table will be synchronized to the container table.

Refresh complete: the data in the container table will be completely deleted, and all data in the base table will be fully synchronized to the container table.

Refresh force: try refresh fast first, and run refresh complete if you fail

Never refresh: never refresh

Refresh rate

On demand: the specified refresh shown, either manually or at specified intervals

On commit: refresh the base table in the same transaction, that is, refresh the base table as soon as it is customized

Query rewrite (query rewrite)

If you want to improve the efficiency of the program, SQL runs frequently, but you can't overwrite SQL (usually multi-table connection). If the SQL sentence is not easy to integrate, you can try using query rewrite to improve performance.

If used, there are two related dynamic parameters

Query_rewrite_enabled:

The default value true enables query rewriting

Query_rewrite_integrity:

Enforced can be used for query rewriting only if the physical and chemical statistics are up to date and the bundle is authenticated (validate). It is a default value.

Trusted can use query rewrite only if the physical statistics are up-to-date and the bundle is not authenticated (novalidate) but must be declared as rely (trust).

Stale_tolerated can use query rewrite even if the materialized statistics are not up to date.

2.query rewrite use case

The following SQL needs to be executed frequently, the execution efficiency is not high, the SQL sentence cannot be translated, and the table data is not changed much.

Select a.emp_no aemp_no,a.emp_name aemp_name,b.emp_no bemp_no,b.emp_name bemp_name

From mes1.emp a,mes1.emp1 b

Where a.emp_no=b.emp_no

The examination uses the materialization query rewrite function and uses refresh fast on commit to quickly refresh the base table when the data is changed.

Build MV:

CREATE MATERIALIZED VIEW mes1.mv_emp

BUILD IMMEDIATE

REFRESH FAST ON COMMIT

ENABLE QUERY REWRITE

AS

Select a.emp_no aemp_no,a.emp_name aemp_name,b.emp_no bemp_no,b.emp_name bemp_name

From mes1.emp a,mes1.emp1 b

Where a.emp_no=b.emp_no

Create a report: ORA-23413: form "MES1". "EMP1" does not have an organized calendar log

Xianming:

Because the refresh fast time base table must have a materialized log

Handle:

Create MATERIALIZED VIEW LOG ON mes1.emp with rowid

Create MATERIALIZED VIEW LOG ON mes1.emp1 with rowid

Execute the build report ORA-01031 again: insufficient rights

Xianming:

It is true that the sys account is executed here, but the mes1 account does not have create table rights. For specific analysis, please see http://blog.itpub.net/4227/viewspace-310155/

Handle:

Grant CREATE table to mes1

Running the build report ORA-12052 again: unable to quickly reorganize the MES1.MV_EMP of the equipment table

Xianming:

Originally, there is no rowid that uses the base table in the definition.

Handle:

SELECT a.ROWID arowid, b.ROWID browid,a.emp_no aemp_no,a.emp_name aemp_name,b.emp_no bemp_no,b.emp_name bemp_name

From mes1.emp a,mes1.emp1 b where a.emp_no=b.emp_no

Perform the construction again:

CREATE MATERIALIZED VIEW mes1.mv_emp

BUILD IMMEDIATE

REFRESH FAST ON COMMIT

ENABLE QUERY REWRITE

AS

SELECT a.ROWID arowid,b.ROWID browid

A.emp_no aemp_no,a.emp_name aemp_name,b.emp_no bemp_no,b.emp_name bemp_name

From mes1.emp a,mes1.emp1 b where a.emp_no=b.emp_no

Success!

Finally, take a look at the query rewrite effect:

SQL > set trace traceonly

SQL > SELECT a.emp_no aemp_no,a.emp_name aemp_name

2 b.emp_no bemp_no,b.emp_name bemp_name

3 FROM mes1.emp a, mes1.emp1 b WHERE a.emp_no = b.emp_no

Execution Plan

Plan hash value: 2244303076

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 3 | 120 | 3 (0) | 00:00:01 |

| | 1 | MAT_VIEW REWRITE ACCESS FULL | MV_EMP | 3 | 120 | 3 (0) | 00:00:01 |

-

Thank you for reading this article carefully. I hope the article "Oracle how to use materialized View query to rewrite query rewrite" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.