Shulou(Shulou.com)06/01 Report--

Optimize it by case study-- establishing materialized View (Materialized View) across databases

Application environment:

Operating system: RedHat EL55

Oracle: Oracle 10gR2

I. Overview of materialized views

The materialized view of Oracle is a database object that includes a query result, which is a local copy of the remote data, or is used to generate a summary table based on the sum of the data table. Materialized views store data based on remote tables, which can also be called snapshots.

Materialized view can be used to pre-calculate and save the results of more time-consuming operations such as table join or aggregation, so that when executing the query, we can avoid these time-consuming operations and get the results quickly. Materialized views are similar to indexes in many aspects: the purpose of using materialized views is to improve query performance; materialized views are transparent to the application, adding and deleting materialized views will not affect the correctness and validity of SQL statements in the application; materialized views need to take up storage space; when the base table changes, materialized views should also be refreshed.

Materialized views can query tables, views, and other materialized views.

Typically, materialized views are called master tables (during replication) or parts lists (in data warehouses).

For replication, materialized views allow you to maintain copies of remote data locally, which are read-only. If you want to modify the local copy, you must use the advanced replication feature. When you want to extract data from a table or view, you can extract it from a materialized view.

For data warehouses, the materialized views created are usually aggregate views, single table aggregate views, and join views.

In a replicated environment, materialized views are usually created with primary keys, rowid, and subquery views.

Materialized views can be indexed because they are physically real.

Second, materialized view refresh

Materialized views can be classified according to different priorities:

1) divided by refresh method: FAST/COMPLETE/FORCE

2) according to the refresh time: ON DEMAND/ON COMMIT

3) press whether it can be updated: UPDATABLE/READ ONLY

4) rewrite by whether the query is supported or not: ENABLE QUERY REWRITE/DISABLEQUERY REWRITE

By default, Oracle defaults to FORCE and DEMAND if you do not specify a refresh method and refresh mode.

Note: the materialized view that sets the REFRESH ON COMMIT cannot access the remote object.

When creating a materialized view, you can specify an ORDER BY statement so that the generated data is saved in a certain order. However, this statement is not written to the definition of the materialized view and has no effect on future refreshes.

There are three ways to refresh materialized views: COMPLETE, FAST, and FORCE.

1) COMPLETE deletes all records in the table (if it is a single table refresh, it may use TRUNCATE), and then regenerates the materialized view according to the definition of the query statement in the materialized view.

2) Fast refresh (FAST) adopts the mechanism of incremental refresh, which only refreshes all operations on the base table to the materialized view since the last refresh. FAST must create a view log based on the primary table.

For the incremental refresh option, the materialized view does not work if there is an analysis function in the subquery.

3) in FORCE mode, Oracle will automatically determine whether the condition for fast refresh is met, and if so, perform a quick refresh, otherwise complete refresh.

The fast refresh mechanism of Oracle materialized view is accomplished through materialized view log. Oracle can also support rapid refresh of multiple materialized views through a materialized view log.

The materialized view log can be established as ROWID or PRIMARY KEY type according to the need for rapid refresh of different materialized views. You can also choose whether to include SEQUENCE, INCLUDING NEW VALUES, and a list of specified columns.

Third, create materialized view command

Create materialized view [view_name]

Refresh [fast | complete | force]

[

On [commit | demand] |

Start with (start_time) next (next_time)

]

As

{query statement used to create materialized view}

Case study:

The framework of this case

1) set up db link on test1 library

Tnsnames.ora:

[oracle@rh7 admin] $cat tnsnames.ora# tnsnames.ora Network Configuration File: / u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.TEST1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = rh7.cuug.net) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test1)) PROD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = rh7.cuug.net) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod) set up tom users on the prod library And authorize 15:18:08 SYS@ prod > create user tom identified by tom User created.15:18:27 SYS@ prod > grant connect,resource to tom;Grant succeeded.15:18:49 SYS@ prod > grant all on scott.emp1 to tom;Grant succeeded. Set up db link15:12:12 SYS@ test1 > grant create database link,create public database link to tom;Grant succeeded.15:13:59 TOM@ test1 > create database link db_link_prod connect to tom identified by tom using 'prod';Database link created on the test1 library. Test: 15:19:10 TOM@ test1 > select * from scott.emp1@db_link_prod EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO--7369 SMITH CLERK 7902 1980-12 -17 00:00:00 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 7499 30 7566 JONES MANAGER 7839 1600 04-02 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 78391981-06-09 00:00:00 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7876 ADAMS CLERK 1987-05-23 00: 00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 1014 rows selected.2) create a materialized view log on the emp1 table of the prod library

15:06:49 SCOTT@ prod > create table emp1 as select * from emp;Table created.15:09:07 SCOTT@ prod > alter table emp1 add constraint pk_emp1 primary key (empno); Table altered.15:09:26 SCOTT@ prod > create materialized view log on emp1;Materialized view log created.

3) establish the materialized view on test1

Authorize 15:19:07 SYS@ prod > grant select any table to tom;Grant succeeded for tom on the prod library. Authorization for tom on the test1 library 15:22:11 SYS@ test1 > grant create materialized view to tom;Grant succeeded.TOM@ test1 > create materialized view mv1_emp1refresh fast on demand as select * from scott.emp1@db_link_prod; Test: 15:33:15 TOM@ test1 > select * from mv1_emp1 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO--7369 SMITH CLERK 7902 1980-12 -17 00:00:00 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 7499 30 7566 JONES MANAGER 7839 1600 04-02 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 78391981-06-09 00:00:00 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7876 ADAMS CLERK 1987-05-23 00: 00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 1014 rows selected.4) Test materialized view data refresh

Update the data on the base table 15:33:10 SYS@ prod > conn scott/tigerConnected.15:35:59 SCOTT@ prod > select * from emp1 where empno=7788 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 15:36:04 SCOTT@ prod > update emp1 set deptno=40 where empno=7788 1 row updated.15:36:23 SCOTT@ prod > commit;Commit complete.15:36:35 SCOTT@ prod > select * from emp1 where empno=7788 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- 7788 SCOTT ANALYST 7566 19-APR-87 3000 40 View data update 15:35:13 TOM@ test1 > select * from mv1_emp1 where empno=7788 in the materialized view EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO--7788 SCOTT ANALYST 7566 1987-04- 19 00:00:00 3000 20 default materialized views are not updated automatically Need to update 15:38:12 TOM@ test1 > exec dbms_mview.refresh ('mv1_emp1','fast') manually PL/SQL procedure successfully completed.15:38:41 TOM@ test1 > select * from mv1_emp1 where empno=7788 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO--7788 SCOTT ANALYST 7566 1987-04- 19 00:00:00 3000 40 so far The establishment of materialized view is complete!

5) View data and log update information

View data refresh information on test1 15:39:02 TOM@ test1 > select mview_name, last_refresh_date, staleness from user_mviews MVIEW_NAME LAST_REFRESH_DATE STALENESS-- MV1_EMP1 2014-08-22 15:38:41 UNDEFINED view on prod Materialized view log update information 15:40:41 SCOTT@ prod > select log_owner Master,log_table,PRIMARY_KEY,LAST_PURGE_DATE LAST_PURGE_STATUS from user_mview_logsLOG_OWNER MASTER LOG_TABLE PRI LAST_PURG LAST_PURGE_STATUS -SCOTT EMP1 MLOG$_EMP1 YES 22-AUG-14 0

6) create an index on the materialized view

15:39:39 TOM@ test1 > CREATE index mv1_ind on mv1_emp1 (ename) tablespace indx;Index created.16:39:15 TOM@ test1 > select index_name,index_type,table_name,BLEVEL,leaf_blocks FROM user_indexes16:39:30 2 where index_name='MV1_IND' INDEX_NAME INDEX_TYPE TABLE_NAME BLEVEL LEAF_BLOCKS-- MV1_IND NORMAL MV1_EMP1 0 116 virtual 40 02 TOM@ test1 > select * from mv1_emp1 where ename='scott' No rows selectedElapsed: 00:00:00.02Execution Plan---Plan hash value: 720877713- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- | 0 | SELECT STATEMENT | | 1 | 87 | 1 (0) | 00:00:01 | | 1 | MAT_VIEW ACCESS BY INDEX ROWID | MV1_EMP1 | 1 | 87 | 1 (0) | 00 | : 00:01 | | * 2 | INDEX RANGE SCAN | MV1_IND | 1 | | 1 (0) | 00:00:01 |- -Predicate Information (identified by operation id):-2-access ("ENAME" = 'scott') Note--dynamic sampling used for this statement (level=2) Statistics- -310 recursive calls 0 db block gets 44 consistent gets 0 physical reads 0 redo size 695 bytes sent via SQL*Net to client 408 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 0 rows processed

Case error message:

Error creating materialized view

15:31:27 TOM@ test1 > create materialized view mv1_emp1

15:32:36 2 refresh fast on demand

15:32:36 3 as

15:32:36 4 select * from scott.emp1@db_link_prod

Create materialized view mv1_emp1

*

ERROR at line 1:

ORA-12018: following error encountered during code generation for "TOM". "MV1_EMP1"

ORA-00942: table or view does not exist

Resolve:

On the library where the base table is located, authorize:

15:19:07 SYS@ prod > grant select any table to tom

Grant succeeded.

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.