具体的Oracle参数视图实践

一般情况下Oracle数据库是不带参数的视图。有时，我们想使用Oracle参数视图，可以给我们方便查询数据。
比如，眼下我面对这这样一个问题：要打印一张报表，报表的数据源是Oracle视图。现在，随着数据量的增大，打印报表的速度越来越慢了。所以首先想到了如何优化视图，视图的基表为3张表，表A几百条数据，表B大约3万条数据，表C大约60万条数据，其中表C每月大约增加3万条数据，视图的SCRIPTS大概是这个样子：

创新互联建站专业为企业提供东台网站建设、东台做网站、东台网站设计、东台网站制作等企业网站建设、网页设计与制作、东台企业网站模板建站服务，10多年东台做网站经验，不只是建网站，更提供有价值的思路和整体网络服务。

 
 
 
  
  
  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()方法设定时间条件，然后查询视图生成报表，这样就变通的实现了带参数视图。#p#

方案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参数化视图可以实现。但想以此作为优化的手段，看来并不可取。

本文题目：具体的Oracle参数视图实践
转载来于：http://www.stwzsj.com/qtweb/news39/3689.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源：创新互联

猜你还喜欢下面的内容