程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> 具體的Oracle參數視圖實踐

具體的Oracle參數視圖實踐

編輯：Oracle數據庫基礎

一般情況下Oracle數據庫是不帶參數的視圖。有時，我們想使用Oracle參數視圖，可以給我們方便查詢數據。
比如，眼下我面對這這樣一個問題：要打印一張報表，報表的數據源是Oracle視圖。現在，隨著數據量的增大，打印報表的速度越來越慢了。所以首先想到了如何優化視圖，視圖的基表為3張表，表A幾百條數據，表B大約3萬條數據，表C大約60萬條數據，其中表C每月大約增加3萬條數據，視圖的SCRIPTS大概是這個樣子：

CREATE OR REPLACE VIEW  M_VIEW  
(COL1,COL2,COL3)  
AS   
SELECT  COL1,COL2,COL3 FROM A,B,C WHERE A.NAME=GET_A_NAME(B.ID) AND B.ID = C.ID;

其中，GET_A_NAME()為已定義好的根據B表ID查詢其對應的A表NAME的方法。各基表中都已建立了索引，考慮到打印報表時值需要指定月份的數據，所以，想到能不能在視圖中限定C.TIME=指定的時間，這樣數據量就會大大下降，只需要C表中大約3萬條數據。但是，問題是，Oracle中視圖是不能帶參數的。有問題，就有辦法，用變通的辦法，看招：

方案1：利用全局變量。
用全局變量做什麼？改一下Oracle參數視圖：

CREATE OR REPLACE VIEW  M_VIEW  
(COL1,COL2,COL3)  
AS   
SELECT  COL1,COL2,COL3 FROM A,B,C WHERE A.NAME=GET_A_NAME(B.ID) AND B.ID = C.ID AND C.TIME=全局變量;

全局變量中保存查詢的時間條件，在每次調用查詢視圖之前，先修改全局變量的值，然後查詢視圖時就會按照指定條件篩選數據。但是，問題又來了，Oracle參數中是不支持全局變量的，要實現全局變量，同樣需要變通實現，方法就是用包（PACKAGE），針對眼前的應用，可以設計如下的包：

CREATE OR REPLACE PACKAGE PKG_REPORT AS  
FUNCTION GET_VALUE RETURN VARCHAR2;  
PROCEDURE SET_VALUE(PS_TIME IN VARCHAR2);  
END PKG_REPORT;  
CREATE OR REPLACE PACKAGE BODY PKG_REPORT IS  
M_V VARCHAR2(6);  
PROCEDURE SET_VALUE(PS_TIME IN VARCHAR2) IS BEGIN M_V:=PS_TIME; END;  
FUNCTION GET_VALUE RETURN VARCHAR2 IS BEGIN RETURN M_V; END;  
END PKG_REPORT;

首先修改上面的視圖，把全局變量替換成PKG_REPORT.GET_VALUE()，在程序中打印報表時先調用PKG_REPORT.SET_VALUE()方法設定時間條件，然後查詢視圖生成報表，這樣就變通的實現了帶參數視圖。

方案2：利用臨時表。
利用臨時表的思路是這樣的：首先將所需數據插入到臨時表中，然後構造視圖時以該臨時表為基表。在Oracle參數中，臨時表分為事務級別、會話級別兩種。會話級別的臨時表，顧名思義是基於SESSION的，在SESSION失效時表中的數據會自動TRUNCATE掉，而且臨時表中的數據各SESSION只能訪問自己的數據，不用考慮並發沖突的問題。可見，會話級別的臨時表正適合我的應用。
首先，建立臨時表：

CREATE GLOBAL TEMPORARY TABLE  M_TEMP_TABLE  
(  
  COL1            VARCHAR2(10 BYTE),  
  COL2            VARCHAR2(80 BYTE),  
  COL3            VARCHAR2(16 BYTE)  
)  
ON COMMIT PRESERVE ROWS  ----指定臨時表是會話級別的  
NOCACHE;

然後，重構視圖：

CREATE OR REPLACE VIEW  M_VIEW  
(COL1,COL2,COL3)  
AS   
SELECT  * FROM M_TEMP_TABLE;  ----以臨時表為基表

最後，在程序中打印報表前，先調用如下過程，往臨時表中插入數據：

CREATE OR REPLACE PROCEDURE PR_DYNAMIC_DATA(  
/************************************  
動態的往會話級別的臨時表中插數據  
**************************************/  
    PS_TIME  IN  VARCHAR2, -----指定的時間條件  
    RTN        OUT    NUMBER  
)  
IS      
BEGIN  
    RTN:=1;        
BEGIN          
     INSERT INTO M_TEMP_TABLE   
     SELECT  COL1,COL2,COL3 FROM A,B,C WHERE A.NAME=GET_A_NAME(B.ID) AND B.ID = C.ID AND C.TIME=PS_TIME;       
    EXCEPTION  
        WHEN OTHERS THEN  
            RTN:=-1;  
    END;  
    IF RTN=1 THEN         
        COMMIT;  
    ELSE  
        ROLLBACK;  
    END IF;      
END;

實際測試，方案看總是看上去很美的，究竟能不能響，還不一定。測試了一下：
優化前：打印報表需要1分45秒；
方案1：打印報表需要1分45秒；
方案2：打印報表需要1分35秒；
可見，方案真的是看上去美，但並沒帶來實際的效果。分析其原因，應該是Oracle自動優化了原來的代碼，也就是說，SELECT * FROM 視圖 A WHERE A.TIME='200710' 和SELECT * FROM 基表 A,基表 B WHERE A.ID=B.ID AND A.TIME='200710' 在執行時應該是一樣的。這只是我的猜想，因為我也沒仔細研究過Oracle優化器怎麼個工作法。
另外，我查看了優化前的視圖腳本、利用方案1優化後的視圖腳本的EXPLAIN PLAN，調整前的COST=36703670，調整後的COST=17031703。應該說能快一些，但實際並不是這個樣兒地，有些不明白了。希望得到指點~

總結：Oracle參數化視圖可以實現。但想以此作為優化的手段，看來並不可取。