[Reprint]oracle materialized view

Original Url:http://lzfhope.blog.163.com/blog/static/636399220124942523943/?suggestedreading&wumii

Environment Oracle 10g 10.2.0.4 Linux 64

See Oracle's own documentation-sql reference for a large and professional look.

To tell the truth, Oracle needs to learn too much, each looking past, it is too cumbersome.

So if you can have an overview of materialized views, that's the most convenient.

Mainly related to content

Materialized view logs for quick refresh of required

Materialized view

permissions, usually not a problem, because the compile time will give you enough permission to prompt, do not find DBA. To practice, you can give DBA authority in a test environment.

What is a materialized view? (a simple definition of materialized views)

In short, it is a view with an entity table, and this view can be refreshed based on a variety of requirements and policies. There is also a very important feature-query rewrite (rewrite). Query rewriting can improve your query speed at some point.

The so-called query rewrite, in short, is that Oracle's query optimizer discovers that the syntax of a materialized view is similar to that of your SQL, then it accesses the materialized view directly rather than the source table in your original query.

What can materialized views do?

Or, what can you do with materialized views?

In a nutshell, here are some important and detailed features that you'll want to include:

1) can improve the query speed, mainly because the materialized view stores the actual data, followed by the query rewrite function. Finally, materialized views have entity tables, and you can also build indexes on them, generally as a table.

2) simplifies development tasks, meaning that developers sometimes do not have to focus directly on the performance of some SQL, and through DBA efforts, use query rewriting to accomplish performance improvements.

3) Reduced effort because materialized views can define two types of refreshes: Refresh immediately and refresh on demand.  The so-called refresh on demand is your own manual refresh, or a timed refresh, the so-called immediate refresh, that is, when the view Main table changes, the view immediately refreshes the content. You can control how the refresh is based on your device situation, application situation, and requirements.

4) The flexible limit of the amount of refresh, you can quickly be refreshed (only the changes are refreshed), or can be fully refreshed. See what you need.

The syntax for materialized views?

This content is too much, or to see Oracle's official SQL Reference bar!

Parameters related to materialized views

Mostly optimization parameters (query rewrite)

Sql> Show parameters Query NAME TYPE VALUE-----------------------------------                 ------------------------------------------query_rewrite_enabled string TRUE query_rewrite_integrity String enforced

The first parameter is whether the query override is supported, and the default is that you can

The second parameter is how query rewriting is supported:

Stale_tolerated: Indicates that materialized views are still used even if the data in the detail table has changed. TRUSTED: This view is used when the materialized view is not invalidated. However, query overrides can use trust relationships, such as those declared by dimension objects or constraints that have not yet been in effect. ENFORCED (default): Indicates that the materialized view is used when it is guaranteed to give the same results as the use of the detail table. Using this parameter means that query rewriting will not use a failed materialized view or trust relationship. The correct setting depends on the data requirements of the application. Query rewriting using the failed materialized view may produce different results than when the query is not being used. However, if you use detail data, you may get worse performance by responding to a large amount of data that the query needs to process. In a data warehouse, it is common to use the trusted full level, as this ensures that you only use materialized views that have the latest data, but relationships that are declared to be correct (trusted) can also be used for query rewriting. In most data warehouses, these relationships have been validated in the extract, transform, and load (ETL) process and therefore no longer need to be validated.  

Tools related to materialized views

Mainly the Dbms_mview package.

For beginners, the main concern is two processes:

Explain_mview, see the related features that SQL supports materialized views.

Explain_rewrite, see if query rewriting is supported for querying SQL.

Explain_mview need to have a table: mv_capabilities_table

sql> desc mv_capabilities_table Name Type Nullable Default Comments--------------- -------------- -------- ------- --------statement_id VARCHAR2 (Y)Mvowner VARCHAR2 (Y)Mvname VARCHAR2 (Y)Capability_name VARCHAR2 (Y)POSSIBLE CHAR (1) YRelated_text VARCHAR2 (Y)Related_num number Ymsgno INTEGER y msgtxt VARCHAR2 (y) SEQ number Y

This will find the script in $oracle_home/rdbms/admin/utlxmv.sql.

Explain_rewrite Required Table: rewrite_table

The script is also in the $ORACLE _home/rdbms/admin/utlxrw.sql.

In addition to these, there are many processes, such as:

SELECT * from Dba_procedures where procedure_name like '%mview% '

SELECT * from Dba_procedures where object_name= ' Dbms_snapshot '

(Dbms_ MVIEW is dbms_snapshot synonym) select * from dba_synonyms where synonym_name like '%dbms_mview% '

(this means that after 10g everyone does not call materialized view again for snapshot).

Introduction to materialized View logs

Materialized views are designed to support fast refreshes

The overall syntax is relatively simple and can be simplified as follows:

Create materilized View Log on XXXX with? [Including|excluding] New values

The new values after including are not allowed.

Log is focused on the two sentences with and new values

The WITH clause tells Oracle if the primary key, line number, object ID, or combination of these identities need to be recorded when the primary table data changes, the approximate syntax diagram is as follows:

Object ID: is a system-generated or user-defined object identifier, and if the source table row changes, the ID of the object is logged. Of course, only the source table is the object table, so you can set it up.

Primary key: When the row changes, the primary key is stored.

ROWID: When changing, store line number.

Sequence: Indicates which additional sorting information needs to be stored. A sequence is useful for a quick refresh of some of the update scenarios.

Column: Indicates which columns are to be stored (into the log). Typically, these columns are filtered or concatenated columns.

Limitations of the WITH statement:

1) Each materialized view log can only be set to one primary Key,rowid,object Id,sequcen, as well as the column list, or the WITH statement after these keywords can only be the last, cannot these with primary key, primary key

2) The primary key column is already implicitly stored in the log, so the primary key column cannot be included in the column statement.

New Values Statement

The new values statement instructs Oracle to save old data and new data to the log in a DML operation with updates. By default, new new values are not logged.

Example:

Create materialized view Log on customers with primary key,rowid; --Support ROWID materialized view and join materialized view.

Create materialized view Log on the sales with ROWID, sequence (amount_sold,time_id,prod_id) including new values; Supports materialized views with statistics (Amount_sold is the column of sum, TIME_ID,PROD_ID is the column used for filtering).

Example view: CREATE materialized view PRODUCTS_MV REFRESH FAST on COMMIT as SELECT SUM (List_price-min_price), category_id from Product_information GROUP by category_id;

materialized view examples :

This gives an example of materialized views that can be updated quickly and in a timely manner, which is relatively simple.

Example 1: A fast refresh materialized view submitted

CREATE TABLE test_1 (ID int,name varchar2, constraint pk_test_1 primary key (ID))

CREATE TABLE test_2 (ID int,score int, constraint pk_test_2 primary key (ID)) drop materialized view Log on Test_1 drop materialized View Log on Test_2 Create materialized view Log on test_1 with ROWID create materialized vie W Log on test_2 with rowID

Create materialized view Mv_fastrefresh refresh fast on commit with ROWID enable query rewrite as select A.id,b.name , A.score,a.rowid Ra,b.rowid RB from test_2 A, test_1 b where b.id=a.id

Example two: Statistical materialized view of fast refresh

CREATE TABLE test_s1 (ID int,name varchar2, constraint PK_TEST_S1 primary key (ID))

CREATE TABLE TEST_S2 (id int,mon int, salary number, constraint Pk_test_s2 primary key (Id,mon)) drop Materializ Ed view Log on TEST_S1; Drop materialized view Log on TEST_S2; Create materialized view Log on test_s1 with ROWID, sequence (id,name) including new values; Create materialized view Log on Test_s2 with ROWID, sequence (id,salary) including new values;

Create materialized view Mv_fastrefresh_sum refresh fast with primary key enable query rewrite as select A.id,b.nam E,sum (a.salary) totalincome from test_s2 A, test_s1 b where b.id=a.id GROUP by A.id,b.name

Add

2012/05/21

Start with or next cannot and on demand, on commit coexistence

-------------------------------------------------------2013, 04,As a result of the work required, often to write such a script, so keep the following:-first check, and then necessary to delete declare Vs_jobname varchar2 (): = ' refresh_dmart_jyr '; Vn_count Pls_integer;     Begin/* Refreshes once at the time of creation, and then refreshes every night around December of the week before and after 23:53 ' */SELECT COUNT (*) into Vn_count from User_scheduler_jobs WHERE Job_name=vs_jobname;IF vn_count>0 then Dbms_scheduler.   Drop_job (vs_jobname,true);   END IF; Dbms_scheduler.create_job (job_name =>vs_jobname , job_type = ' Plsql_block ', job_action = ' BEGIN DB Ms_mview. REFRESH (' Mv_distinct_jyr ', ' C ');     END; ' , start_date = systimestamp, repeat_interval = ' freq=yearly; bydate=1231^span:1w; byhour=23;  Byminute=53 ', enabled = TRUE, Auto_drop = FALSE, comments = ' Refresh the system trading day '); Dbms_scheduler.run_job (Vs_jobname); end;

[Reprint]oracle materialized view