建站
咨詢
售后
建站咨詢
新聞中心
一般情況下Oracle數(shù)據(jù)庫是不帶參數(shù)的視圖。有時(shí)，我們想使用Oracle參數(shù)視圖，可以給我們方便查詢數(shù)據(jù)。
比如，眼下我面對(duì)這這樣一個(gè)問題：要打印一張報(bào)表，報(bào)表的數(shù)據(jù)源是Oracle視圖。現(xiàn)在，隨著數(shù)據(jù)量的增大，打印報(bào)表的速度越來越慢了。所以首先想到了如何優(yōu)化視圖，視圖的基表為3張表，表A幾百條數(shù)據(jù)，表B大約3萬條數(shù)據(jù)，表C大約60萬條數(shù)據(jù)，其中表C每月大約增加3萬條數(shù)據(jù)，視圖的SCRIPTS大概是這個(gè)樣子：
創(chuàng)新互聯(lián)建站專業(yè)為企業(yè)提供東臺(tái)網(wǎng)站建設(shè)、東臺(tái)做網(wǎng)站、東臺(tái)網(wǎng)站設(shè)計(jì)、東臺(tái)網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計(jì)與制作、東臺(tái)企業(yè)網(wǎng)站模板建站服務(wù)，10多年東臺(tái)做網(wǎng)站經(jīng)驗(yàn)，不只是建網(wǎng)站，更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。
- 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()為已定義好的根據(jù)B表ID查詢其對(duì)應(yīng)的A表NAME的方法。各基表中都已建立了索引，考慮到打印報(bào)表時(shí)值需要指定月份的數(shù)據(jù)，所以，想到能不能在視圖中限定C.TIME=指定的時(shí)間，這樣數(shù)據(jù)量就會(huì)大大下降，只需要C表中大約3萬條數(shù)據(jù)。但是，問題是，Oracle中視圖是不能帶參數(shù)的。有問題，就有辦法，用變通的辦法，看招：
方案1：利用全局變量。
用全局變量做什么？改一下Oracle參數(shù)視圖：
- 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=全局變量;
全局變量中保存查詢的時(shí)間條件，在每次調(diào)用查詢視圖之前，先修改全局變量的值，然后查詢視圖時(shí)就會(huì)按照指定條件篩選數(shù)據(jù)。但是，問題又來了，Oracle參數(shù)中是不支持全局變量的，要實(shí)現(xiàn)全局變量，同樣需要變通實(shí)現(xiàn)，方法就是用包（PACKAGE），針對(duì)眼前的應(yīng)用，可以設(shè)計(jì)如下的包：
- 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()，在程序中打印報(bào)表時(shí)先調(diào)用PKG_REPORT.SET_VALUE()方法設(shè)定時(shí)間條件，然后查詢視圖生成報(bào)表，這樣就變通的實(shí)現(xiàn)了帶參數(shù)視圖。#p#
方案2：利用臨時(shí)表。
利用臨時(shí)表的思路是這樣的：首先將所需數(shù)據(jù)插入到臨時(shí)表中，然后構(gòu)造視圖時(shí)以該臨時(shí)表為基表。在Oracle參數(shù)中，臨時(shí)表分為事務(wù)級(jí)別、會(huì)話級(jí)別兩種。會(huì)話級(jí)別的臨時(shí)表，顧名思義是基于SESSION的，在SESSION失效時(shí)表中的數(shù)據(jù)會(huì)自動(dòng)TRUNCATE掉，而且臨時(shí)表中的數(shù)據(jù)各SESSION只能訪問自己的數(shù)據(jù)，不用考慮并發(fā)沖突的問題。可見，會(huì)話級(jí)別的臨時(shí)表正適合我的應(yīng)用。
首先，建立臨時(shí)表：
- CREATE GLOBAL TEMPORARY TABLE M_TEMP_TABLE
- (
- COL1 VARCHAR2(10 BYTE),
- COL2 VARCHAR2(80 BYTE),
- COL3 VARCHAR2(16 BYTE)
- )
- ON COMMIT PRESERVE ROWS ----指定臨時(shí)表是會(huì)話級(jí)別的
- NOCACHE;
然后，重構(gòu)視圖：
- CREATE OR REPLACE VIEW M_VIEW
- (COL1,COL2,COL3)
- AS
- SELECT * FROM M_TEMP_TABLE; ----以臨時(shí)表為基表
***，在程序中打印報(bào)表前，先調(diào)用如下過程，往臨時(shí)表中插入數(shù)據(jù)：
- CREATE OR REPLACE PROCEDURE PR_DYNAMIC_DATA(
- /************************************
- 動(dòng)態(tài)的往會(huì)話級(jí)別的臨時(shí)表中插數(shù)據(jù)
- **************************************/
- PS_TIME IN VARCHAR2, -----指定的時(shí)間條件
- 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;
實(shí)際測(cè)試，方案看總是看上去很美的，究竟能不能響，還不一定。測(cè)試了一下：
優(yōu)化前：打印報(bào)表需要1分45秒；
方案1：打印報(bào)表需要1分45秒；
方案2：打印報(bào)表需要1分35秒；
可見，方案真的是看上去美，但并沒帶來實(shí)際的效果。分析其原因，應(yīng)該是Oracle自動(dòng)優(yōu)化了原來的代碼，也就是說，SELECT * FROM 視圖 A WHERE A.TIME='200710' 和SELECT * FROM 基表 A,基表 B WHERE A.ID=B.ID AND A.TIME='200710' 在執(zhí)行時(shí)應(yīng)該是一樣的。這只是我的猜想，因?yàn)槲乙矝]仔細(xì)研究過Oracle優(yōu)化器怎么個(gè)工作法。
另外，我查看了優(yōu)化前的視圖腳本、利用方案1優(yōu)化后的視圖腳本的EXPLAIN PLAN，調(diào)整前的COST=36703670，調(diào)整后的COST=17031703。應(yīng)該說能快一些，但實(shí)際并不是這個(gè)樣兒地，有些不明白了。希望得到指點(diǎn)~
總結(jié)：Oracle參數(shù)化視圖可以實(shí)現(xiàn)。但想以此作為優(yōu)化的手段，看來并不可取。
文章名稱：具體的Oracle參數(shù)視圖實(shí)踐
文章位置：http://fisionsoft.com.cn/article/ccspdid.html
