One-on-One Oracle閱讀筆記2(轉)

11.9 最後說明
　　建立概要需要CREATE ANY
　　OUTLINE許可權，若無許可權，利用ALTER
　　SESSION方式來建立概要時不會提示錯誤，但不會建立概要。
　　刪除使用者時即便指定CASCADE選項，也不會刪除其建立的概要。
　　如果CURSOR_SHARING設為force，則用DDL和ALTER
　　SESSION兩種方法獲得的SQL文字可能是不同的，前者就是輸入的SQL，而後者是系統內部已經轉換過繫結變數的SQL。
　　概要的使用依靠文字完全匹配，即便是大小寫不同也會造成SQL文字不匹配。
　　OR擴充套件問題：由於WHERE條件中有OR的SQL會被改寫為UNION
　　ALL模式，概要記錄的提示可能無法正常使用，而只是作用到第一個條件上。因此要注意USER_OUTLINE_HINTS表中HINT LIKE
　　‘USE_CONCAT%’的概要和提示，應當刪除或移走。
　　使用概要對效能影響很小。建立概要時接近首次分析該條語句的時間，此後第一次分析慢於正常分析時間，而隨後概要已經進入快取，將不會觀察到效能影響。
　　11.10 可能遇到的錯誤
　　ORA-18001 – 使用ALTER OUTLINE語法錯誤
　　ORA-18002 – 所引用的概要不存在（從未建立過或者被刪除）
　　ORA-18003 – 概要的數字簽名已存在，數字簽名用於快速查詢到合適的概要，此錯誤極少發生
　　ORA-18004 – 概要已存在，一般是命名衝突
　　ORA-18005 – 需要CREATE ANY OUTLINE許可權
　　ORA-18006 – 需要DROP ANY OUTLINE許可權
　　ORA-18007 – 需要ALTER ANY OUTLINE許可權
　　
　　
　　
　　
　　第 12
　　章 分析函式
　　12.1 分析函式如何工作
　　語法
　　FUNCTION_NAME(,…)
　　OVER
　　(　　表示式,…> >
　　)
　　PARTITION子句
　　ORDER BY子句
　　WINDOWING子句
　　預設時相當於RANGE UNBOUNDED PRECEDING
　　1. 值域窗（RANGE WINDOW）
　　
　　RANGE N PRECEDING
　　僅對數值或日期型別有效，選定窗為排序後當前行之前，某列（即排序列）值大於/小於（當前行該列值
　　–/+ N）的所有行，因此與ORDER BY子句有關係。
　　2. 行窗（ROW WINDOW）
　　
　　ROWS N PRECEDING
　　選定窗為當前行及之前N行。
　　還可以加上BETWEEN AND 形式，例如RANGE BETWEEN m PRECEDING AND n
　　FOLLOWING
　　函式
　　
　　AVG( expr)
　　一組或選定窗中表示式的平均值
　　CORR(expr,
　　expr)
　　即COVAR_POP(exp1,exp2) / (STDDEV_POP(expr1) *
　　STDDEV_POP(expr2))，兩個表示式的互相關，-1（反相關） ~ 1（正相關），0表示不相關
　　COUNT( )
　　計數
　　COVAR_POP(expr, expr)
　　總體協方差
　　COVAR_SAMP(expr, expr)
　　樣本協方差
　　CUME_DIST
　　累積分佈，即行在組中的相對位置，返回0 ~ 1
　　DENSE_RANK
　　行的相對排序（與ORDER BY搭配），相同的值具有一樣的序數（NULL計為相同），並不留空序數
　　FIRST_VALUE
　　一個組的第一個值
　　LAG(expr,
　　, )
　　訪問之前的行，OFFSET是預設為1 的正數，表示相對行數，DEFAULT是當超出選定窗範圍時的返回值（如第一行不存在之前行）
　　LAST_VALUE
　　一個組的最後一個值
　　LEAD(expr,
　　, )
　　訪問之後的行，OFFSET是預設為1 的正數，表示相對行數，DEFAULT是當超出選定窗範圍時的返回值（如最後行不存在之前行）
　　MAXexpr)
　　最大值
　　MIN(expr)
　　最小值
　　NTILE(expr)
　　按表示式的值和行在組中的位置編號，如表示式為4，則組分4份，分別為1 ~ 4的值，而不能等分則多出的部分在值最小的那組
　　PERCENT_RANK
　　類似CUME_DIST，1/(行的序數 - 1)
　　RANK
　　相對序數，允許並列，並空出隨後序號
　　RATIO_TO_REPORT(expr)
　　表示式值 / SUM(表示式值)
　　REGR_
　　xxxx(expr, expr)
　　線性迴歸函式
　　ROW_NUMBER
　　排序的組中行的偏移
　　STDDEV(expr)
　　標準差
　　STDDEV_POP(expr)
　　總體標準差
　　STDDEV_SAMP(expr)
　　樣本標準差
　　SUM(expr)
　　合計
　　VAR_POP(expr)
　　總體方差
　　VAR_SAMP(expr)
　　樣本方差
　　VARIANCE(expr)
　　方差
　　12.2 例子
　　豎錶轉橫表
　　一般形式為將一個列為C!, C2, … CN的表，以C1, C2, … CX為基準，將CX+1, … CN的不同值改為列。一般化的語法：
　　SELECT C1, C2, … CX,
　　
　　MAX(DECODE(rn, 1, CX+1, NULL)) CX+1_1, … MAX(DECODE(rn, 1, CN, NULL))
　　CN_1
　　
　　MAX(DECODE(rn, 2, CX+1, NULL)) CX+1_2, … MAX(DECODE(rn, 2, CN, NULL))
　　CN_2
　　
　　…
　　
　　MAX(DECODE(rn,N,CX+1, NULL)) CX+1_N, … MAX(DECODE(rn,N, CN, NULL))
　　CN_N
　　FROM
　　(SELECT C1, C2, …
　　CN,
　　ROW_NUMBER() OVER (PARTITION
　　BY C1, C2, … CX ORDER BY ) rn
　　
　　FROM T
　　
　　WHERE …)
　　GROUP BY C1, C2, … CX;
　　
　　通用包：
　　CREATE OR REPLACE PACKAGE
　　pkg_pivot
　　AS
　　
　　TYPE refcursor IS REF CURSOR;
　　
　　TYPE ARRAY IS TABLE OF VARCHAR2(30);
　　
　　PROCEDURE pivot(p_max_cols IN NUMBER DEFAULT
　　NULL,
　　 p_max_cols_query IN VARCHAR2
　　DEFAULT NULL,
　　 p_query IN
　　VARCHAR2,
　　 p_anchor IN
　　ARRAY,
　　 p_pivot IN
　　ARRAY,
　　 p_cursor IN OUT
　　refcursor);
　　END;
　　
　　CREATE OR REPLACE PACKAGE BODY
　　pkg_pivot
　　AS
　　
　　PROCEDURE pivot(p_max_cols IN NUMBER DEFAULT
　　NULL,
　　 p_max_cols_query IN VARCHAR2
　　DEFAULT NULL,
　　 p_query IN
　　VARCHAR2,
　　 p_anchor IN
　　ARRAY,
　　 p_pivot IN
　　ARRAY,
　　 p_cursor IN OUT
　　refcursor)
　　
　　AS
　　
　　l_max_cols NUMBER;
　　
　　l_query LONG;
　　
　　l_cnames ARRAY;
　　
　　BEGIN
　　
　　IF (p_max_cols IS NOT NULL)
　　
　　THEN
　　 EXECUTE IMMEDIATE p_max_cols_query INTO
　　l_max_cols;
　　
　　ELSE
　　 RAISE_APPLICATION_ERROR(-20001, 'Cannot
　　figure out max cols');
　　
　　END IF;
　　
　　
　　l_query := 'select ';
　　
　　FOR i IN 1 .. p_anchor.count
　　
　　LOOP
　　 l_query := l_query || p_anchor(i) ||
　　',';
　　
　　END LOOP;
　　
　　
　　FOR i IN 1 .. l_max_cols
　　
　　LOOP
　　 FOR j IN 1 ..
　　p_pivot.count
　　 LOOP
　　 l_query := l_query ||
　　'max(decode(rn,'||i||','||p_pivot(j)||',null)) '||p_pivot(j) || '_' || i ||
　　',';
　　 END LOOP;
　　
　　END LOOP;
　　
　　
　　l_query := RTRIM(l_query,',') || ' from (' || p_query || ') group by
　　';
　　
　　FOR i IN 1 .. p_anchor.count
　　
　　LOOP
　　 l_query := l_query || p_anchor(i) ||
　　',';
　　
　　END LOOP;
　　
　　l_query := RTRIM(l_query,',');
　　
　　
　　EXECUTE IMMEDIATE 'alter session set
　　cursor_sharing=force';
　　
　　OPEN p_cursor FOR l_query;
　　
　　EXECUTE IMMEDIATE 'alter session set
　　cursor_sharing=exact';
　　
　　END;
　　END;
　　其中：
　　p_max_cols_query為SELECT MAX(COUNT(*)) FROM TABLE_NAME GROUP BY C1, C2, …
　　CX;
　　p_query為SELECT C1, C2, … CN ROW_NUMBER() OVER (PARTITION BY C1, C2, … CX
　　ORDER BY ) rn FROM TABLE_NAME;
　　p_anchor為pkg_pivot.array(C1, C2, … CX)
　　p_pivot為pkg_pivot.array(CX+1, CX+2, … CN)
　　p_cursor為返回的遊標。
　　12.3 最後說明
　　PL/SQL與分析函式
　　PL/SQL不支援分析函式的語法，可以通過以下兩種方法解決：
　　１．使用動態遊標；
　　２．將含分析函式的語句建立為檢視。
　　WHERE子句中的分析函式
　　由於查詢僅在最後的ORDER BY子句前執行分析函式，因此WHERE條件中無法使用分析函式，只能利用巢狀迴圈實現。
　　
　　
　　
　　
　　第 13
　　章 物化檢視
　　8.1.5企業版/個人版開始支援
　　需要許可權：GRANT CREATE MATERIALIZED VIEW，還必須直接賦予GRANT QUERY REWRITE。為實現查詢重寫，必須使用CBO。
　　13.1 物化檢視如何工作
　　設定
　　COMPATIBLE引數必須高於8.1.0
　　QUERY_REWRITE_ENABLED = TRUE
　　QUERY_REWRITE_INTEGRETY =
　　
　　ENFORCED - 查詢僅用Oracle強制與保證的約束、規則重寫；
　　
　　TRUSTED – 查詢除用Oracle強制與保證的約束、規則，也可用使用者設定的資料間的任何關係來重寫；
　　
　　STALE_TOLERATED – 即便Oracle知道物化檢視中資料過期（與事實表等不同步），也重寫查詢。
　　建立物化檢視的使用者必須具有直接賦予的GRANT QUERY REWRITE許可權，不能通過角色繼承。
　　內部機制
　　全文匹配
　　部分匹配：從FROM子句開始，優化器比較之後的文字，然後比較SELECT列表
　　一般重寫方法：
　　資料充分
　　關聯相容
　　分組相容
　　聚集相容
　　13.2 確保使用物化檢視
　　約束
　　考慮到現實環境的資料量，可以將主鍵、外來鍵、非空等約束置為NOVALIDATE，並調整QUERY_REWRITE_INTEGRITY為TRUSTED，這樣可以達到“欺騙”資料庫的目的，但必須注意如果無法保證此類約束的真實有效，查詢改寫後可能造成結果不精確。
　　維度
　　實際就是指明已存在的表中各列的歸併關係，從而關聯事實表後形成的物化檢視可用於向“上”歸併（相當於用表中代表更高歸併關係的列關聯事實表）。標準語法：
　　CREATE DIMENSION
　　time_hierarchy_dim
　　
　　LEVEL day IS
　　time_hierarchy.day
　　
　　LEVEL mmyyyy IS
　　time_hierarchy.mmyyyy
　　
　　LEVEL yyyy IS
　　time_hierarchy.yyyy
　　HIERARCHY time_rollup
　　(day CHILD OF mmyyyy CHILD OF
　　yyyy)
　　ATTRIBUTE mmyyyy
　　DETERMINES mon_yyyy;
　　13.3
　　DBMS_OLAP
　　估計（物化檢視）大小
　　DBMS_OLAP.ESTIMATE_SUMMARY_SIZE(檢視名, 檢視定義, 估計行數, 估計位元組數);
　　其中後兩個引數為NUMBER型輸出引數。
　　維度有效性檢查
　　DBMS_OLAP.VALIDATE_DIMENSION(檢視名, 使用者名稱, FALSE, FALSE);
　　SELECT * FROM 維度表名
　　WHERE ROWIN IN (SEELCT bad_rowid FROM
　　MVIEW$_EXCEPTION);
　　所選出行即為不符合維度定義的行。
　　推薦物化檢視
　　首先必須新增合適的外來鍵，包通過外來鍵來判定表之間的關係而不是維度。
　　DBMS_OLAP.RECOMMEND_MV(事實表名, 1000000000, ‘’);
　　第二個參數列示物化檢視可用的空間大小，可傳入一個較大的數。第三個引數傳入需要保留的特定物化檢視，傳入空即為不考慮其他物化檢視。
　　執行C:oracleRDBMSdemosadvdemo後執行：
　　DEMO_SUMADV.PRETTYPRINT_RECOMMENDATIONS
　　13.4 最後說明
　　物化檢視不為OLTP系統設計
　　在事實表等更新時會導致物化檢視行鎖，從而影響系統併發性。
　　第 14
　　章 分割槽
　　14.1 分割槽的使用
　　增加可用性
　　減輕維護負擔
　　提高DML與查詢的效能
　　14.2 分割槽如何工作
　　表分割槽策略
　　索引分割槽
　　本地索引
　　分為本地字首索引（Local Prefixed Index）、本地非字首索引（Local Non-prefixed Index）
　　1． 索引的選擇
　　在單表查詢中，本地非字首索引可能增加可用性，也更加實用。例如表T(a, b)按a區間分割槽，若在b上建立本地索引，則當某個分割槽離線，僅查詢b的某個值時，該索引可用，而索引(a, b)不可用；刪除索引(a, b)，查詢(a, b)的某對值，b上的索引仍可用。此時若建立索引(b, a)，則可應對各類查詢。
　　在多表關聯（如上例中按照(a, b)值關聯）時，系統將發現代價較高而不會用到本地非字首索引（如上例中(b, a)）。
　　因此建立本地索引時應當考慮通常的使用環境。
　　2． 無法基於本地非字首索引建立唯一鍵或主鍵。
　　全域性索引
　　僅有一種，即全域性字首索引
　　1. 資料倉儲環境
　　在（與建立好相應索引的表）交換分割槽與索引或分割分割槽後，全域性索引將失效而必須重建，因此全域性索引並不適合資料倉儲。
　　例如：
　　ALTER TABLE
　　partitioned
　　EXCHANGE PARTITION
　　fy_1999
　　WITH TABLE
　　fy_1999
　　INCLUDING INDEXES
　　WITHOUT
　　VALIDATION;
　　
　　ALTER TABLE
　　partitioned
　　SPLIT PARTITION
　　the_rest
　　AT
　　(TO_DATE(‘200101’,
　　’yyyymm’))
　　INTO (PARTITION fy_2000, PARTITION
　　the_rest);
　　2. OLTP環境
　　一定程度上增加了可用性。當某些分割槽離線，不含有用於分割槽的列且合乎查詢條件的資料存在於線上分割槽的索引仍然是可用的，對於不需要查詢全表而是通過索引即可得到結果的查詢也是有效的（例如COUNT非用於分割槽的列等）。
　　
　　
　　第 15
　　章 自治事務
　　DECLARE
　　PRAGMA
　　AUTONOMOUS_TRANSACTION;
　　15.1 為何使用自治事務
　　無法回滾的審計
　　一般情況下利用觸發器禁止某些對錶的更新等操作時，若記錄日誌，則觸發器最後丟擲異常時會造成日誌回滾。利用自治事務可防止此點。
　　避免變異表
　　即在觸發器中操作觸發此觸發器的表
　　在觸發器中使用DDL
　　寫資料庫
　　對資料庫有寫操作（INSERT、UPDATE、DELETE、CREATE、ALTER、COMMIT）的儲存過程或函式是無法簡單的用SQL來呼叫的，此時可以將其設為自治事務，從而避免ORA-14552（無法在一個查詢或DML中執行DDL、COMMIT、ROLLBACK）、ORA-14551（無法在一個查詢中執行DML操作）等錯誤。需要注意的是函式必須有返回值，但僅有IN引數（不能有OUT或IN/OUT引數）。
　　開發更模組化的程式碼
　　在大型開發中，自治事務可以將程式碼更加模組化，失敗或成功時不會影響呼叫者的其它操作，代價是呼叫者失去了對此模組的控制，並且模組內部無法引用呼叫者未提交的資料。
　　15.2 如何工作
　　事務控制
　　DECLARE整個塊都是屬於父事務的，自治事務從離PRAGMA後的第一個BEGIN開始，只要此BEGIN塊仍在作用域，則都屬於自治事務。例如在DECLARE模組中宣告一個寫資料庫的函式，則此函式雖然在自治事務所在儲存過程執行，但其屬於父事務；而自治事務中呼叫的任何函式和儲存過程、激發的任何觸發器等均為此自治事務的一部分。
　　自治事務可以巢狀，巢狀深度等只受INIT.ORA引數TRANSACTIONS（同時併發的事務數，預設為SESSIONS的1.1倍）制約。
　　作用域
　　1. 包中的變數
　　自治事務可看到並修改父事務的變數，父事務也會察覺到這一改變，且不存在回滾問題。
　　2. 會話設定/引數
　　自治事務與父事務共享同一個會話環境，通過ALTER SESSION作的修改對整個會話均有效。但SET TRANSACTION是事務級的，僅對提起修改的事務有效。
　　3. 資料庫修改
　　父事務已提交的修改對自治事務可見，未提交的對自治事務不可見，自治事務的修改對父事務是否可見取決於隔離級別（Isolation Level）。
　　對於遊標，取決於其開啟的位置，若其在父事務中開啟，則之前父事務未提交的修改對其是有效的，在自治事務中這些修改也可見；而在自治事務中開啟，則父事務未提交的修改不可見。
　　若使用預設的READ COMMITTED隔離級別，則自治事務的修改對父事務可見；若改用SERIALIZABLE，則不可見。
　　4. 鎖
　　父事務與自治事務是完全不同的事務，因此無法共享鎖等。
　　結束一個自治事務
　　必須提交一個COMMIT、ROLLBACK或執行DDL。
　　儲存點
　　無法在自治事務中回滾到父事務中的一個儲存點，只能在內部使用儲存點。
　　15.3 最後說明
　　不支援分散式事務
　　截至8.1.7在自治事務中不支援分散式事務
　　僅可用PL/SQL
　　全部事務回滾
　　若自治事務出錯，則全部回滾，即便父事務有異常處理模組。
　　事務級臨時表
　　每個會話僅一個事務可訪問事務級臨時表（多個會話中的事務可併發操作）。
　　變異表
　　15.4 可能遇到的錯誤
　　ORA-06519 – 檢查到活動自治事務，回滾——退出自治事務時沒有提交、回滾或DDL操作
　　ORA-14450 – 試圖訪問正在使用的事務級臨時表
　　ORA-00060 – 等待資源時檢查到死鎖
　　
　　
　　 第 16 章 動態SQL
　　16.1 為何使用動態SQL
　　實現動態SQL有兩種方式：DBMS_SQL和本地動態SQL（EXECUTE IMMEIDATE）
　　主要從以下方面考慮使用哪種方式：
　　1. 是否知道涉及的列數和型別
　　DBMS_SQL包括了一個可以“描述”結果集的儲存過程（DBMS_SQL.DESCRIBE_COLUMNS），而本地動態SQL沒有。
　　2. 是否知道可能涉及的繫結變數數和型別
　　DBMS_SQL允許過程化的繫結語句的輸入，而本地動態SQL需要在編譯時確定。
　　3. 是否使用“陣列化”操作（Array
　　Processing）
　　DBMS_SQL允許，而本地動態SQL基本不可以，但可以用其他方式實現（對查詢可用FETCH BULK
　　COLLECT INTO，對INSERT等，可用一個BEGIN …
　　END塊中加迴圈實現）。
　　4. 是否在同一個會話中多次執行同一語句
　　DBMS_SQL可以分析一次執行多次，而本地動態SQL會在每次執行時進行軟分析。
　　5. 是否需要用REF
　　CURSOR返回結果集
　　僅本地動態SQL可用REF
　　CURSOR返回結果集。
　　16.2 如何使用動態SQL
　　DBMS_SQL
　　1. 呼叫OPEN_CURSOR獲得一個遊標控制程式碼；
　　2. 呼叫PARSE分析語句。一個遊標控制程式碼可以用於多條不同的已分析語句，但一個時間點僅一條有效；
　　3. 呼叫BIND_VARIABLE或BIND_ARRAY來提供語句的任何輸入；
　　4. 若是一個查詢（SELECT語句），呼叫DIFINE_COLUMN或DEFINE_ARRAY來告知Oracle如何返回結果；
　　5. 呼叫EXECUTE執行語句；
　　6. 若是一個查詢，呼叫FETCH_ROWS來讀取資料。可以使用COLUMN_VALUE從SELECT列表根據位置獲得這些值；
　　7. 否則，若是一個PL/SQL塊或帶有RETURN子句的DML語句，可以呼叫VARIABLE_VALUE從塊中根據變數名獲得OUT值；
　　8. 呼叫CLOSE_CURSOR。
　　注意這裡對任何異常都應該處理，以關閉遊標，防止洩露資源。
　　本地動態SQL
　　EXECUTE
　　IMMEDIATE ‘語句’
　　[INTO
　　{變數1,
　　變數2, …
　　變數N |
　　記錄體}]
　　[USING [IN
　　| OUT | IN OUT] 繫結變數1, …
　　繫結變數N]
　　[{RETURNING
　　| RETURN} INTO 輸出1 [, …,
　　輸出N]…];
　　注意本地動態SQL僅支援弱型別REF
　　CURSOR，即對於REF CURSOR，不支援BULK
　　COLLECT。
　　16.3 最後說明
　　動態SQL的負面：破壞了依賴鏈、程式碼更脆弱、很難調優。
　　
　　
　　
　　第 17
　　章
　　interMedia
　　
　　 第 18 章 基於C的外部過程

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242223/，如需轉載，請註明出處，否則將追究法律責任。