1818IP-服务器技术教程,云服务器评测推荐,服务器系统排错处理,环境搭建,攻击防护等

当前位置:首页 - 数据库 - 正文

君子好学,自强不息!

具体的Oracle参数视图实践

2022-11-24 | 数据库 | gtxyzz | 382°c
A+ A-

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

CREATEORREPLACEVIEWM_VIEW 
  • (COL1,COL2,COL3) AS
  • SELECTCOL1,COL2,COL3FROMA,B,CWHEREA.NAME=GET_A_NAME(B.ID)ANDB.ID=C.ID;
  • 其中,GET_A_NAME()为已定义好的根据B表ID查询其对应的A表NAME的方法。各基表中都已建立了索引,考虑到打印报表时值需要指定月份的数据,所以,想到能不能在视图中限定C.TIME=指定的时间,这样数据量就会大大下降,只需要C表中大约3万条数据。但是,问题是,Oracle中视图是不能带参数的。有问题,就有办法,用变通的办法,看招:

    方案1:利用全局变量。 用全局变量做什么?改一下Oracle参数视图:

    CREATEORREPLACEVIEWM_VIEW 
  • (COL1,COL2,COL3) AS
  • SELECTCOL1,COL2,COL3FROMA,B,CWHEREA.NAME=GET_A_NAME(B.ID)ANDB.ID=C.IDANDC.TIME=全局变量;
  • 全局变量中保存查询的时间条件,在每次调用查询视图之前,先修改全局变量的值,然后查询视图时就会按照指定条件筛选数据。但是,问题又来了,Oracle参数中是不支持全局变量的,要实现全局变量,同样需要变通实现,方法就是用包(PACKAGE),针对眼前的应用,可以设计如下的包:

    CREATEORREPLACEPACKAGEPKG_REPORTAS 
  • FUNCTIONGET_VALUERETURNVARCHAR2; PROCEDURESET_VALUE(PS_TIMEINVARCHAR2);
  • ENDPKG_REPORT; CREATEORREPLACEPACKAGEBODYPKG_REPORTIS
  • M_VVARCHAR2(6); PROCEDURESET_VALUE(PS_TIMEINVARCHAR2)ISBEGINM_V:=PS_TIME;END;
  • FUNCTIONGET_VALUERETURNVARCHAR2ISBEGINRETURNM_V;END; ENDPKG_REPORT;
  • 首先修改上面的视图,把全局变量替换成PKG_REPORT.GET_VALUE(),在程序中打印报表时先调用PKG_REPORT.SET_VALUE()方法设定时间条件,然后查询视图生成报表,这样就变通的实现了带参数视图。#p#

    方案2:利用临时表。 利用临时表的思路是这样的:首先将所需数据插入到临时表中,然后构造视图时以该临时表为基表。在Oracle参数中,临时表分为事务级别、会话级别两种。会话级别的临时表,顾名思义是基于SESSION的,在SESSION失效时表中的数据会自动TRUNCATE掉,而且临时表中的数据各SESSION只能访问自己的数据,不用考虑并发冲突的问题。可见,会话级别的临时表正适合我的应用。 首先,建立临时表:

    CREATEGLOBALTEMPORARYTABLEM_TEMP_TABLE 
  • ( COL1VARCHAR2(10BYTE),
  • COL2VARCHAR2(80BYTE), COL3VARCHAR2(16BYTE)
  • ) ONCOMMITPRESERVEROWS----指定临时表是会话级别的
  • NOCACHE;
  • 然后,重构视图:

    CREATEORREPLACEVIEWM_VIEW 
  • (COL1,COL2,COL3) AS
  • SELECT*FROMM_TEMP_TABLE;----以临时表为基表
  • ***,在程序中打印报表前,先调用如下过程,往临时表中插入数据:

    CREATEORREPLACEPROCEDUREPR_DYNAMIC_DATA( 
  • /************************************ 动态的往会话级别的临时表中插数据
  • **************************************/ PS_TIMEINVARCHAR2,-----指定的时间条件
  • RTNOUTNUMBER )
  • IS BEGIN
  • RTN:=1; BEGIN
  • INSERTINTOM_TEMP_TABLE SELECTCOL1,COL2,COL3FROMA,B,CWHEREA.NAME=GET_A_NAME(B.ID)ANDB.ID=C.IDANDC.TIME=PS_TIME;
  • EXCEPTION WHENOTHERSTHEN
  • RTN:=-1; END;
  • IFRTN=1THEN COMMIT;
  • ELSE ROLLBACK;
  • ENDIF; 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参数化视图可以实现。但想以此作为优化的手段,看来并不可取。

    本文来源:1818IP

    本文地址:https://www.1818ip.com/post/11452.html

    免责声明:本文由用户上传,如有侵权请联系删除!

    发表评论

    必填

    选填

    选填

    ◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。