通過案例學調優之--跨庫建立物化視圖（Materialized View）

應用環境：

作業系統： RedHat EL55

Oracle： Oracle 10gR2

Oracle的物化視圖是包括一個查詢結果的資料庫對像，它是遠端資料的的本地副本，或者用來生成基于資料表求和的彙總表。物化視圖存儲基于遠端表的資料，也可以稱為快照。

物化視圖可以用于預先計算并儲存表連接配接或聚集等耗時較多的操作的結果，這樣，在執行查詢時，就可以避免進行這些耗時的操作，而從快速的得到結果。物化視圖有很多方面和索引很相似：使用物化視圖的目的是為了提高查詢性能；物化視圖對應用透明，增加和删除物化視圖不會影響應用程式中SQL語句的正确性和有效性；物化視圖需要占用存儲空間；當基表發生變化時，物化視圖也應當重新整理。

物化視圖可以查詢表，視圖和其它的物化視圖。

通常情況下，物化視圖被稱為主表（在複制期間）或明細表（在資料倉庫中）。

對于複制，物化視圖允許你在本地維護遠端資料的副本,這些副本是隻讀的。如果你想修改本地副本，必須用進階複制的功能。當你想從一個表或視圖中抽取資料時，你可以用從物化視圖中抽取。

對于資料倉庫，建立的物化視圖通常情況下是聚合視圖，單一表聚合視圖和連接配接視圖。

在複制環境下，建立的物化視圖通常情況下主鍵，rowid和子查詢視圖。

物化視圖由于是實體真實存在的，故可以建立索引。

二、物化視圖重新整理

1) 按重新整理方式分：FAST/COMPLETE/FORCE

2) 按重新整理時間的不同：ON DEMAND/ON COMMIT

3) 按是否可更新：UPDATABLE/READ ONLY

4) 按是否支援查詢重寫：ENABLE QUERY REWRITE/DISABLEQUERY REWRITE

預設情況下，如果沒指定重新整理方法和重新整理模式，則Oracle預設為FORCE和DEMAND。

注意：設定REFRESH ON COMMIT的物化視圖不能通路遠端對象。

在建立物化視圖的時候可以指定ORDER BY語句，使生成的資料按照一定的順序進行儲存。不過這個語句不會寫入物化視圖的定義中，而且對以後的重新整理也無效。

1) 完全重新整理（COMPLETE）會删除表中所有的記錄（如果是單表重新整理，可能會采用TRUNCATE的方式），然後根據物化視圖中查詢語句的定義重新生成物化視圖。

2) 快速重新整理（FAST）采用增量重新整理的機制，隻将自上次重新整理以後對基表進行的所有操作重新整理到物化視圖中去。FAST必須建立基于主表的視圖日志。

對于增量重新整理選項，如果在子查詢中存在分析函數，則物化視圖不起作用。

3) 采用FORCE方式，Oracle會自動判斷是否滿足快速重新整理的條件，如果滿足則進行快速重新整理，否則進行完全重新整理。

Oracle物化視圖的快速重新整理機制是通過物化視圖日志完成的。Oracle通過一個物化視圖日志還可以支援多個物化視圖的快速重新整理。

物化視圖日志根據不同物化視圖的快速重新整理的需要，可以建立為ROWID或PRIMARY KEY類型的。還可以選擇是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的清單。

三、建立物化視圖指令

create materialized view [view_name]

refresh [fast|complete|force]

[

on [commit|demand] |

start with (start_time) next (next_time)

]

{建立物化視圖用的查詢語句}

案例分析：

本案例架構

1）在test1庫上建立db link

tnsnames.ora:

<code>[oracle@rh6 admin]$ cat tnsnames.ora</code>

<code># tnsnames.ora Network Configuration File: /u01/app/oracle/product/</code><code>11.2.</code><code>0</code><code>/db_1/network/admin/tnsnames.ora</code>

<code># Generated by Oracle configuration tools.</code>

<code> </code><code>(DESCRIPTION =</code>

<code> </code><code>(ADDRESS = (PROTOCOL = TCP)(HOST = rh6.cuug.net)(PORT = </code><code>1521</code><code>))</code>

<code> </code><code>(CONNECT_DATA =</code>

<code> </code><code>(SERVER = DEDICATED)</code>

<code> </code><code>(SERVICE_NAME = test1)</code>

<code> </code><code>(SERVICE_NAME = prod)</code>

<code>在prod庫上建立tom使用者，并授權</code>

<code>15</code><code>:</code><code>18</code><code>:</code><code>08</code> <code>SYS@ prod >create user tom identified by tom;</code>

<code>User created.</code>

<code>15</code><code>:</code><code>18</code><code>:</code><code>27</code> <code>SYS@ prod >grant connect,resource to tom;</code>

<code>Grant succeeded.</code>

<code>15</code><code>:</code><code>18</code><code>:</code><code>49</code> <code>SYS@ prod >grant all </code><code>on</code> <code>scott.emp1 to tom;</code>

<code>在test1庫上建立db link</code>

<code>15</code><code>:</code><code>12</code><code>:</code><code>12</code> <code>SYS@ test1 >grant create database link,create </code><code>public</code> <code>database link to tom;</code>

<code>15</code><code>:</code><code>13</code><code>:</code><code>59</code> <code>TOM@ test1 >create database link db_link_prod connect to tom identified by tom using </code><code>'prod'</code><code>;</code>

<code>Database link created.</code>

<code>15</code><code>:</code><code>19</code><code>:</code><code>10</code> <code>TOM@ test1 >select * </code><code>from</code> <code>scott.emp1@db_link_prod;</code>

<code> </code><code>EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO</code>

<code> </code><code>7369</code> <code>SMITH CLERK </code><code>7902</code> <code>1980</code><code>-12</code><code>-17</code> <code>00</code><code>:</code><code>00</code><code>:</code><code>00</code> <code>800</code> <code>20</code>

<code> </code><code>7499</code> <code>ALLEN SALESMAN </code><code>7698</code> <code>1981</code><code>-02</code><code>-20</code> <code>00</code><code>:</code><code>00</code><code>:</code><code>00</code> <code>1600</code> <code>300</code> <code>30</code>

<code> </code><code>7521</code> <code>WARD SALESMAN </code><code>7698</code> <code>1981</code><code>-02</code><code>-22</code> <code>00</code><code>:</code><code>00</code><code>:</code><code>00</code> <code>1250</code> <code>500</code> <code>30</code>

<code> </code><code>7566</code> <code>JONES MANAGER </code><code>7839</code> <code>1981</code><code>-04</code><code>-02</code> <code>00</code><code>:</code><code>00</code><code>:</code><code>00</code> <code>2975</code> <code>20</code>

<code> </code><code>7654</code> <code>MARTIN SALESMAN </code><code>7698</code> <code>1981</code><code>-09</code><code>-28</code> <code>00</code><code>:</code><code>00</code><code>:</code><code>00</code> <code>1250</code> <code>1400</code> <code>30</code>

<code> </code><code>7698</code> <code>BLAKE MANAGER </code><code>7839</code> <code>1981</code><code>-05</code><code>-01</code> <code>00</code><code>:</code><code>00</code><code>:</code><code>00</code> <code>2850</code> <code>30</code>

<code> </code><code>7782</code> <code>CLARK MANAGER </code><code>7839</code> <code>1981</code><code>-06</code><code>-09</code> <code>00</code><code>:</code><code>00</code><code>:</code><code>00</code> <code>2450</code> <code>10</code>

<code> </code><code>7788</code> <code>SCOTT ANALYST </code><code>7566</code> <code>1987</code><code>-04</code><code>-19</code> <code>00</code><code>:</code><code>00</code><code>:</code><code>00</code> <code>3000</code> <code>20</code>

<code> </code><code>7839</code> <code>KING PRESIDENT </code><code>1981</code><code>-11</code><code>-17</code> <code>00</code><code>:</code><code>00</code><code>:</code><code>00</code> <code>5000</code> <code>10</code>

<code> </code><code>7844</code> <code>TURNER SALESMAN </code><code>7698</code> <code>1981</code><code>-09</code><code>-08</code> <code>00</code><code>:</code><code>00</code><code>:</code><code>00</code> <code>1500</code> <code>0</code> <code>30</code>

<code> </code><code>7876</code> <code>ADAMS CLERK </code><code>7788</code> <code>1987</code><code>-05</code><code>-23</code> <code>00</code><code>:</code><code>00</code><code>:</code><code>00</code> <code>1100</code> <code>20</code>

<code> </code><code>7900</code> <code>JAMES CLERK </code><code>7698</code> <code>1981</code><code>-12</code><code>-03</code> <code>00</code><code>:</code><code>00</code><code>:</code><code>00</code> <code>950</code> <code>30</code>

<code> </code><code>7902</code> <code>FORD ANALYST </code><code>7566</code> <code>1981</code><code>-12</code><code>-03</code> <code>00</code><code>:</code><code>00</code><code>:</code><code>00</code> <code>3000</code> <code>20</code>

<code> </code><code>7934</code> <code>MILLER CLERK </code><code>7782</code> <code>1982</code><code>-01</code><code>-23</code> <code>00</code><code>:</code><code>00</code><code>:</code><code>00</code> <code>1300</code> <code>10</code>

<code>14</code> <code>rows selected.</code>

2）在prod庫的emp1表上建立物化視圖日志

<code>15</code><code>:</code><code>06</code><code>:</code><code>49</code> <code>SCOTT@ prod >create table emp1 </code><code>as</code> <code>select * </code><code>from</code> <code>emp;</code>

<code>Table created.</code>

<code>15</code><code>:</code><code>09</code><code>:</code><code>07</code> <code>SCOTT@ prod >alter table emp1 add constraint pk_emp1 primary key(empno);</code>

<code>Table altered.</code>

<code>15</code><code>:</code><code>09</code><code>:</code><code>26</code> <code>SCOTT@ prod >create materialized view log </code><code>on</code> <code>emp1;</code>

<code>Materialized view log created.</code>

3）在test1上建立物化視圖

<code>在prod庫上對tom授權</code>

<code>15</code><code>:</code><code>19</code><code>:</code><code>07</code> <code>SYS@ prod >grant select any table to tom;</code>

<code>在test1庫上對tom授權</code>

<code>15</code><code>:</code><code>22</code><code>:</code><code>11</code> <code>SYS@ test1 >grant create materialized view to tom;</code>

<code>TOM@ test1 >create materialized view mv1_emp1</code>

<code>refresh fast </code><code>on</code> <code>demand</code>

<code> </code><code>select * </code><code>from</code> <code>scott.emp1@db_link_prod;</code>

<code>15</code><code>:</code><code>33</code><code>:</code><code>15</code> <code>TOM@ test1 >select * </code><code>from</code> <code>mv1_emp1;</code>

4）測試物化視圖資料重新整理

<code>在基表上更新資料</code>

<code>15</code><code>:</code><code>33</code><code>:</code><code>10</code> <code>SYS@ prod >conn scott/tiger</code>

<code>Connected.</code>

<code>15</code><code>:</code><code>35</code><code>:</code><code>59</code> <code>SCOTT@ prod >select * </code><code>from</code> <code>emp1 </code><code>where</code> <code>empno=</code><code>7788</code><code>;</code>

<code> </code><code>EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO</code>

<code> </code><code>7876</code> <code>ADAMS CLERK </code><code>7788</code> <code>23</code><code>-MAY</code><code>-87</code> <code>1100</code> <code>20</code>

<code>15</code><code>:</code><code>36</code><code>:</code><code>04</code> <code>SCOTT@ prod >update emp1 set deptno=</code><code>40</code> <code>where</code> <code>empno=</code><code>7788</code><code>;</code>

<code>1</code> <code>row updated.</code>

<code>15</code><code>:</code><code>36</code><code>:</code><code>23</code> <code>SCOTT@ prod >commit;</code>

<code>Commit complete.</code>

<code>15</code><code>:</code><code>36</code><code>:</code><code>35</code> <code>SCOTT@ prod >select * </code><code>from</code> <code>emp1 </code><code>where</code> <code>empno=</code><code>7788</code><code>;</code>

<code> </code><code>7788</code> <code>SCOTT ANALYST </code><code>7566</code> <code>19</code><code>-APR</code><code>-87</code> <code>3000</code> <code>40</code>

<code>在物化視圖上檢視資料更新</code>

<code>15</code><code>:</code><code>35</code><code>:</code><code>13</code> <code>TOM@ test1 >select * </code><code>from</code> <code>mv1_emp1 </code><code>where</code> <code>empno=</code><code>7788</code><code>;</code>

<code>預設物化視圖不會自動更新，需手工更新</code>

<code>15</code><code>:</code><code>38</code><code>:</code><code>12</code> <code>TOM@ test1 >exec dbms_mview.refresh(</code><code>'mv1_emp1'</code><code>,</code><code>'fast'</code><code>);</code>

<code>PL/SQL procedure successfully completed.</code>

<code>15</code><code>:</code><code>38</code><code>:</code><code>41</code> <code>TOM@ test1 >select * </code><code>from</code> <code>mv1_emp1 </code><code>where</code> <code>empno=</code><code>7788</code><code>;</code>

至此，物化視圖建立完成！

5）檢視資料和日志更新資訊

<code>在test1上檢視資料重新整理資訊</code>

<code>15</code><code>:</code><code>39</code><code>:</code><code>02</code> <code>TOM@ test1 >select mview_name, last_refresh_date, staleness from user_mviews;</code>

<code>MVIEW_NAME LAST_REFRESH_DATE STALENESS</code>

<code>MV1_EMP1 </code><code>2014</code><code>-</code><code>08</code><code>-</code><code>22</code> <code>15</code><code>:</code><code>38</code><code>:</code><code>41</code> <code>UNDEFINED</code>

<code>在prod上檢視物化視圖日志更新資訊</code>

<code>15</code><code>:</code><code>40</code><code>:</code><code>41</code> <code>SCOTT@ prod >select log_owner,master,log_table,PRIMARY_KEY,LAST_PURGE_DATE,LAST_PURGE_STATUS from user_mview_logs</code>

<code>LOG_OWNER MASTER LOG_TABLE PRI LAST_PURG LAST_PURGE_STATUS</code>

<code>SCOTT EMP1 MLOG$_EMP1 YES </code><code>22</code><code>-AUG-</code><code>14</code> <code>0</code>

6）在物化視圖上建立索引

<code>15</code><code>:</code><code>39</code><code>:</code><code>39</code> <code>TOM@ test1 >CREATE index mv1_ind </code><code>on</code> <code>mv1_emp1(ename) tablespace indx;</code>

<code>Index created.</code>

<code>16</code><code>:</code><code>39</code><code>:</code><code>15</code> <code>TOM@ test1 >select index_name,index_type,table_name,BLEVEL,leaf_blocks FROM user_indexes</code>

<code>16</code><code>:</code><code>39</code><code>:</code><code>30</code> <code>2</code> <code>where</code> <code>index_name=</code><code>'MV1_IND'</code><code>;</code>

<code>INDEX_NAME INDEX_TYPE TABLE_NAME BLEVEL LEAF_BLOCKS</code>

<code>MV1_IND NORMAL MV1_EMP1 </code><code>0</code> <code>1</code>

<code>16</code><code>:</code><code>40</code><code>:</code><code>02</code> <code>TOM@ test1 >select * </code><code>from</code> <code>mv1_emp1 </code><code>where</code> <code>ename=</code><code>'scott'</code><code>;</code>

<code>no rows selected</code>

<code>Elapsed: </code><code>00</code><code>:</code><code>00</code><code>:</code><code>00.02</code>

<code>Execution Plan</code>

<code>Plan hash value: </code><code>720877713</code>

<code>Predicate Information (identified by operation id):</code>

<code> </code><code>2</code> <code>- access(</code><code>"ENAME"</code><code>=</code><code>'scott'</code><code>)</code>

<code> </code><code>- dynamic sampling used </code><code>for</code> <code>this</code> <code>statement (level=</code><code>2</code><code>)</code>

<code>Statistics</code>

<code> </code><code>310</code> <code>recursive calls</code>

<code> </code><code>0</code> <code>db block gets</code>

<code> </code><code>44</code> <code>consistent gets</code>

<code> </code><code>0</code> <code>physical reads</code>

<code> </code><code>695</code> <code>bytes sent via SQL*Net to client</code>

<code> </code><code>408</code> <code>bytes received via SQL*Net </code><code>from</code> <code>client</code>

<code> </code><code>1</code> <code>SQL*Net roundtrips to/</code><code>from</code> <code>client</code>

<code> </code><code>2</code> <code>sorts (memory)</code>

<code> </code><code>0</code> <code>sorts (disk)</code>

<code> </code><code>0</code> <code>rows processed</code>

案例錯誤資訊：

建立物化視圖出錯

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

解決：

在基表所在的庫上，進行授權：

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

Grant succeeded.

本文轉自客居天涯 51CTO部落格，原文連結：http://blog.51cto.com/tiany/1543572，如需轉載請自行聯系原作者

通過案例學調優之--跨庫建立物化視圖（Materialized View）

繼續閱讀

ASP程式設計中20個非常有用的例子

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

賦予使用者dba權限--解釋以上：

實驗樓sql進階之成績管理系統的資料操作(window)

Oracle的基本操作

為什麼要選擇UniDAC

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

windows不能在本地計算機上運作oracleDbConsoleorcl

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離

Oracle 批量查詢傳入List 傳回List