.Net程式員學用Oracle系列(23)：視圖理論、物化視圖

来源:http://www.cnblogs.com/hanzongze/archive/2017/05/09/Oracle-View.html
-Advertisement-
Play Games

" 1、視圖理論 " "1.1、視圖的存儲" "1.2、視圖的作用" "1.3、視圖的工作機制" "1.4、視圖的依賴性" "1.5、可更新的連接視圖" "1.6、內聯視圖" " 2、物化視圖 " "2.1、刷新物化視圖" "2.2、物化視圖日誌" "2.3、管理物化視圖" "2.4、物化視圖與索引 ...


我曾遇到一個項目的資料庫中視圖比表還要多很多（表和視圖加起來上千個），幾乎每個表都有對應的視圖，而且有很多視圖長得相似，比如有些視圖關聯的表一樣，只是查詢列表多或少了一兩個欄位。我敢斷定，這就是因為一些水平低劣的開發人員看現有表或視圖的數據不完全符合他的需要，就不假思索的拷貝過來改一下然後創建一個新的視圖完事兒，久而久之，自然會出現很多相似甚至相同的視圖。

在接手上述項目後，真正令我感到崩潰的是，查詢語句往往涉及多個視圖，且常常是相似的視圖定義中又引用了另一個相似的視圖，被引用的視圖中又引用了另一個相似的視圖……。總之就一個字——亂！有時候為了找與頁面上欄位對應的資料庫欄位得看完一堆的視圖定義才能找到，因此那段時間我特別厭惡視圖！並暗下決心：日後如果我能做主，絕不用視圖。

後來我換了公司，新接觸的項目資料庫中基本沒用視圖。結果沒過多久，我便發現代碼中有很多相似甚至重覆的查詢語句，而且代碼中的查詢語句改起來也比較費勁，於是乎我對視圖的主觀意識發生了搖擺。我開始問自己：到底該不該用視圖？如果用，要怎麼用才合理？用了視圖到底會降低性能還是會提升性能？

最後我通過閱讀《Oracle Database Concepts》並反覆實踐和揣摩，終於對視圖有所感悟。多學一點知識就少一點困惑，而我正是對視圖理論方面有了大致瞭解後才解開對視圖的各種困惑。本文第 1 節大部分內容是對官方手冊的簡化翻譯，也夾雜了一些我個人的體會，英語好的讀者也可以忽略本節直接閱讀原文。

1、視圖理論

視圖是對一或多個表或其他視圖中包含的數據的自定義呈現。視圖將查詢的輸出視為一個表，因此可以認為視圖就是存儲的查詢或虛擬表。視圖中包含行和列，就像一個真實的表，但不包含數據本身。視圖所引用的表被稱為基表，視圖總是呈現基表中最近的數據，視圖可支持更新數據，所有對視圖數據的更新都將被反映到視圖的基表中，並受到基表的完整性約束和觸發器的約束。

1.1、視圖的存儲

與表不同，視圖不需要分配存儲空間，視圖也不包含實際數據。視圖由查詢定義，該查詢從視圖基表中提取或導出數據。因為視圖是基於其他對象的，所以視圖只需要在數據字典中存儲定義視圖的查詢，而不需要額外的存儲空間。

1.2、視圖的作用

可通過視圖以不同的形式來顯示基表中的數據，而視圖的強大之處在於它能夠根據不同的用戶需求來對基表中的數據進行不同形式的整合。視圖的常見用途如下：

  • 1、通過限制對基表中一組指定行或列的訪問，來提供額外的安全控制。
  • 2、視圖允許通過表連接整合多個表中的相關行或列，構成一個新的數據集，從而達到隱藏數據複雜性的目的。
  • 3、視圖提供了從多個表中查詢數據，而不必知道如何關聯這些表的可能，因而簡化了查詢的 SQL 語句。
  • 4、重命名視圖的列或更改數據形式，不會影響視圖所引用的基表，這樣一來就能以不同的角度來呈現基表中的數據了。
  • 5、保存複雜的查詢，一個查詢可能會對錶數據進行複雜的計算，如果將這個查詢保存為視圖，那之後需求進行計算只需查詢該視圖即可。
  • 6、表達不使用視圖無法表達的查詢，有時候用戶需求過於複雜，幾乎寫不出來僅從基表中查詢數據的單條 SQL 語句，如某些複雜的分組查詢、聯合查詢等。

簡單來說，合理運用視圖，不僅可以提高數據的安全性，還可以少寫代碼，提升開發效率和程式的可維護性，也有利於在某些情況下靈活高效的控制數據的展現形式。

1.3、視圖的工作機制

Oracle 將定義視圖的語句以文本的形式存儲在數據字典中。當用戶在 SQL 語句中引用了視圖時，Oracle 將完成以下三步動作：

  • 1、將引用了視圖的語句與視圖的定義語句合併成一個語句。
  • 2、在共用 SQL 區解析整合後的語句。
  • 3、執行該語句。

如果共用 SQL 區中存在相似語句，Oracle 就不會重覆解析，只有在共用 SQL 區中沒有相似語句時，Oracle 才會為該語句創建新的共用 SQL 區。因此引用了視圖的 SQL 語句也可能會節約記憶體進而提高查詢性能。

1.4、視圖的依賴性

定義視圖的查詢必須要引用其它對象（表、視圖），換而言之，視圖依賴於其所引用的對象而存在。Oracle 會自動地處理視圖的依賴關係。例如，當用戶刪除視圖的某個基表後再次創建它，Oracle 就會自動的去檢查新的基表是否符合現有的視圖定義，進而判斷視圖的有效性。在 PL/SQL Developer 中，所有 Oracle 認為無效的視圖都會被打上紅叉叉。

1.5、可更新的連接視圖

連接視圖是指在視圖定義的查詢的 FROM 字句中引用了多個表或視圖的視圖，而可更新的連接視圖是指能夠支持 UPDATE、INSERT 和 DELETE 操作的連接視圖。數據字典視圖ALL_UPDATABLE_COLUMNS、DBA_UPDATABLE_COLUMNS和USER_UPDATABLE_COLUMNS中包含了那些可更新的視圖列信息。如果要確保視圖可更新，那麼視圖定義中就不能包含以下語法結構：

  • 1、集合運算符。
  • 2、DISTINCT 運算符。
  • 3、聚合函數或分析函數。
  • 4、GROUP BY、ORDER BY、CONNECT BY 或 START WITH 子句。
  • 5、SELECT 列表中的集合表達式。
  • 6、SELECT 列表中的子查詢。
  • 7、JOIN 連接（也有例外情況）。

如果視圖只引用了一個表，且包含該表的主鍵，一般就可以對視圖進行 DML 操作。對於不支持 DML 操作的視圖，如包含上述語法結構的連接視圖，還可以使用 INSTEAD OF 觸發器來更新數據。INSTEAD OF 觸發器也被譯作替代觸發器，由於替代觸發器的特殊性，它永遠都是在操作執行前被觸發。語法如下：

CREATE [OR REPLACE] TRIGGER trg_name
INSTEAD OF [[INSERT] [OR UPDATE | UPDATE OF 列名[,列表,...]] [OR DELETE]] ON schema.v_name
[FOR EACH ROW]
[WHEN 觸發條件]
[DECLARE]
  [聲明變數部分;]
BEGIN
  IF INSERTING THEN
    -- TODO: 添加
  ELSIF UPDATING THEN
    -- TODO: 修改
  ELSIF DELETING THEN
    -- TODO: 刪除
  ELSE
    NULL;
  END IF;
END [trg_name];

1.6、內聯視圖

內聯視圖不是模式對象，而是一個有別名的子查詢，一般定義在 FROM 字句之後，可以在 SQL 語句中像使用普通視圖一樣的使用。內聯視圖是一種臨時視圖，不會存儲到數據字典中，它和標準視圖的主要區別是：不需要在執行 SQL 語句之前進行解析和創建共用 SQL 區。

2、物化視圖

物化視圖是包含查詢結果的資料庫對象，用於彙總、計算、複製及分發數據。物化視圖能夠預先計算並保存表連接或聚集等耗時較多的操作結果，這樣，在執行查詢時，就可以避免進行這些耗時的操作，從而快速的得到結果。物化視圖是一種特殊的物理表，“物化”視圖是相對普通視圖而言的。普通視圖是虛擬表，應用的局限性大，任何對視圖的查詢，Oracle 都實際上轉換為視圖 SQL 語句的查詢，這樣對整體查詢性能的提高，並沒有實質上的好處。

2.1、刷新物化視圖

Oracle 在對主表進行 DML 操作之後，會通過刷新來維護物化視圖中的數據（以確保物化視圖和基表中的數據同步）。刷新模式有兩種：ON DEMAND 和 ON COMMIT，而刷新方式有四種：FAST、COMPLETE、FORCE 和 NEVER。FAST 刷新採用增量刷新，只刷新自上次刷新以後進行的修改。COMPLETE 刷新對整個物化視圖進行完全的刷新。如果選擇FORCE 方式，Oracle 會在刷新前先判斷下是否可以進行快速刷新，如果可以則採用 FAST 刷新，否則採用 COMPLETE 刷新。NEVER 指物化視圖不進行任何刷新。

對於使用快速刷新方法的物化視圖，物化視圖日誌或直接載入日誌將保留對主表的更改記錄。已經創建好的物化視圖也還可以再修改它的刷新方式。物化視圖還可以按要求定期刷新。

2.2、物化視圖日誌

物化視圖日誌是將更改同步到主表的模式對象。通過物化視圖日誌可以逐級刷新主表上定義的物化視圖，此過程被稱為增量或快速刷新。如果沒有物化視圖日誌，Oracle 必須重新執行物化視圖查詢來刷新物化視圖，這個過程稱為完全刷新。通常，快速刷新比完全刷新需要更少的時間。

物化視圖日誌位於和主表相同模式中的主資料庫中。每個主表上最多能定義一個物化視圖日誌。Oracle 可以根據物化視圖日誌對所有需要快速刷新的物化視圖執行快速刷新。要快速刷新物化連接視圖，必須為實例化視圖引用的每個表創建一個物化視圖日誌。

創建物化視圖日誌：

示例一（創建支持主鍵實例化視圖快速刷新的物化視圖日誌，並指定物理和存儲特性）：

CREATE MATERIALIZED VIEW LOG ON demo.t_staff
  PCTFREE 5 -- 塊保留的空間百分比
  TABLESPACE users
  STORAGE (INITIAL 10K NEXT 10K);

示例二（創建支持快速刷新 ROWID 物化視圖和物化連接視圖的物化視圖日誌）：

CREATE MATERIALIZED VIEW LOG ON demo.t_staff WITH PRIMARY KEY,ROWID;

示例三（創建支持快速刷新物化聚合視圖的物化視圖日誌）：

CREATE MATERIALIZED VIEW LOG ON demo.t_staff
  WITH ROWID, SEQUENCE(staff_id)
  INCLUDING NEW VALUES;

更多創建物化視圖日誌的細節請參考：《Oracle Database SQL Reference: CREATE MATERIALIZED VIEW LOG》。修改物化視圖日誌的細節請參考：《Oracle Database SQL Reference: ALTER MATERIALIZED VIEW LOG》。

刪除物化視圖日誌：語法結構和刪除表相似，詳細請參考《Oracle Database SQL Reference: DROP MATERIALIZED VIEW》。

DROP MATERIALIZED VIEW LOG ON demo.t_staff;

2.3、管理物化視圖

創建物化視圖：

語法：

CREATE MATERIALIZED VIEW
  [ schema. ]materialized_view
  [ column_alias [, column_alias]... ]
  [ OF [ schema. ]object_type ]
  [ (scoped_table_ref_constraint) ]
  { ON PREBUILT TABLE
    [ { WITH | WITHOUT } REDUCED PRECISION ]
  | physical_properties materialized_view_props
  }
  [ USING INDEX
    [ physical_attributes_clause
    | TABLESPACE tablespace
    ]
      [ physical_attributes_clause
      | TABLESPACE tablespace
      ]...
  | USING NO INDEX
  ]
  [ create_mv_refresh ]
  [ FOR UPDATE ]
  [ { DISABLE | ENABLE }
    QUERY REWRITE
  ]
  AS subquery ;

示例一：

CREATE MATERIALIZED VIEW LOG ON demo.t_staff
  WITH PRIMARY KEY
  INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW mv_staff
  BUILD IMMEDIATE
  REFRESH FAST ON COMMIT
  AS SELECT t1.staff_id,t1.staff_name,DECODE(t1.gender,1,'男',0,'女','未知') gender
FROM demo.t_staff t1
WHERE t1.is_disabled=0;

示例二：

CREATE MATERIALIZED VIEW mv_staff2
AS SELECT t1.staff_id,t1.staff_name,DECODE(t1.gender,1,'男',0,'女','未知') gender,
          EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM t1.birthday) age
   FROM demo.t_staff t1
   WHERE t1.is_disabled=0;

更多創建物化視圖的細節請參考：《Oracle Database SQL Reference: CREATE MATERIALIZED VIEW》。修改物化視圖的細節請參考：《Oracle Database SQL Reference: ALTER MATERIALIZED VIEW》。

刪除物化視圖：語法結構和刪除表相似，詳細請參考《Oracle Database SQL Reference: DROP MATERIALIZED VIEW》。

DROP MATERIALIZED VIEW mv_staff2;

2.4、物化視圖與索引

物化視圖和索引都是為提高資料庫性能而存在的，所以它們有一定的相似之處。列舉如下：

  • 1、它們都消耗存儲空間。
  • 2、當主表中的數據發生更改時，都需要刷新。
  • 3、當它們用於查詢時，都可以提高 SQL 執行的性能。
  • 4、它們的存在對 SQL 應用程式和用戶是透明的（能看到且可以直接操作的意思）。
  • 5、可以對它們進行分區。

物化視圖與索引不同點之一是：物化視圖可以在 SQL 語句中直接訪問，而索引是否生效取決於 Oracle 系統。

3、總結

本文主要講述了普通視圖的基本原理和物化視圖基本用法，希望能對讀者有所幫助。另外，由於我本人物化視圖用的也不多，所以講述的比較片面和淺顯，需要進一步瞭解的讀者可以再看看官網手冊：《Oracle Database Concepts: Overview of Materialized Views》。

本文鏈接：http://www.cnblogs.com/hanzongze/p/Oracle-View.html
版權聲明：本文為博客園博主 韓宗澤 原創，作者保留署名權！歡迎通過轉載、演繹或其它傳播方式來使用本文，但必須在明顯位置給出作者署名和本文鏈接！本人初寫博客，水平有限，若有不當之處，敬請批評指正，謝謝！


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Android--字元串和Drawable之間互相轉化
    //將字元串轉化成Drawable public synchronized static Drawable StringToDrawable(String icon) { if (icon == null || icon.length() < 10) return null; byte[] img ... ...
  • 微信小程式開發《一》：阿裡雲tomcat免費配置https
    小狼咕咕最近開啟了微信小程式開發的徵程，由於微信小程式的前後臺通信必須通過https協議，所以小狼咕咕第一件要做的事就是配置一個能夠通過https訪問的後臺服務。小狼咕咕用的是阿裡雲ECS伺服器，Linux系統，安裝的tomcat。 打開阿裡雲盾——CA證書服務，找不到的朋友也可以在下圖的菜單中直接 ...
  • SQL SERVER大話存儲結構（2）_非聚集索引如何查找到行記錄
    如果轉載，請註明博文來源： www.cnblogs.com/xinysu/ ，版權歸 博客園 蘇家小蘿蔔 所有。望各位支持！ 1 行記錄如何存儲 這裡引入兩個概念：堆跟聚集索引表。本部分參考MSDN。 1.1 堆表 堆表，沒有聚集索引的表格，可以創建一個或者多個非聚集索引。沒有按照某個規則進行存儲， ...
  • 手把手教你安裝mac版hadoop2.7.3教程
    一.準備教程 1.jdk：版本在1.7.x以上就可以（因為hadoop2.x以上只支持1.7.x以上的jdk,我的是1.8的） 2.Hadoop:2.7.3 二.ssh的配置以及驗證 配置ssh： 1.確認mac的遠程登錄是否開啟 系統偏好設置->共用->勾選遠程登錄. 當遠程登錄狀態為打開且為綠燈 ...
  • oracle分區表按時間自動創建
    表分區是一種思想，分區表示一種技術實現。當表的大小過G的時候可以考慮進行表分區，提高查詢效率，均衡IO。oracle分區表是oracle資料庫提供的一種表分區的實現形式。表進行分區後，邏輯上仍然是一張表，原來的查詢SQL同樣生效，同時可以採用使用分區查詢來優化SQL查詢效率，不至於每次都掃描整個表。 ...
  • Linux Mysql root密碼忘記,初始化
    前段時間一個測試環境的mysql資料庫的root密碼找不到了，一個不重要的庫，安裝人員估計疏忽了...手動把密碼恢復了一下。記錄下來做個備註 1、進入my.cnf ,在[mysqld]欄位中添加 skip-grant-tables 2、重啟mysql服務 service mysqld restart ...
  • 關係資料庫如何快速查詢表的記錄數
    在資料庫中，很多人員習慣使用SELECT COUNT（*）、SELECT COUNT（1)、SELECT COUNT(COL)來查詢一個表有多少記錄，對於小表，這種SQL的開銷倒不是很大，但是對於大表，這種查詢表記錄數的做法就是一個非常消耗資源了，而且效率很差。下麵介紹一下SQL Server、 O... ...
  • sqlserver 關聯查詢 charindex 函數 查詢慢，不使用索引問題解決辦法
    問題： 兩張表 數據都非常多 A表中A1欄位 需要關聯B表主鍵 查詢 A1 欄位 存儲多個B表主鍵 格式為： 格式1：b1,b2,b3 格式2：b4 格式3：b5,b6 逗號分隔的占少數 這樣就導致在做關聯查詢時，必須使用 like '%...%'， charindex ，又或者replace（A1 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類