利用query

申请删除 [复制链接]
查看11 | 回复0 | 2020-10-20 01:13:55 | |阅读模式
1问题一般为了解决性能问题可能需要使用outline、sqlprofile固定执行计划。dbms_advanced_rewrite重写SQL甚至可能通过重写改变查询结果。2条件--权限executeondbms_advanced_rewritecreatematerializedview--回话或系统级设置query_rewrite_integeritytrusted/stale_tolerated(enfored为缺省值)?3测试3.1版本SQLselect*fromv$version;BANNER----------------------------------------------------------------------------OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-ProductionPL/SQLRelease11.2.0.1.0-ProductionCORE???11.2.0.1.0?????ProductionTNSforLinux:Version11.2.0.1.0-ProductionNLSRTLVersion11.2.0.1.0-Production3.2准备用户SQLcreateusertomidentifiedbytom;Usercreated.SQLgrantconnect,resourcetotom;Grantsucceeded.SQLgrantexecuteondbms_advanced_rewritetotom;Grantsucceeded.?3.3准备测试表SQLcreatetablet1(xint);Tablecreated.SQLcreatetablet2(xint);Tablecreated.SQLinsertintot1values(1);1rowcreated.SQLr?1*insertintot1values(1)1rowcreated.SQLcommit;Commitcomplete.3.4声明等价性begin?SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE(??name????????????TOM.SQL1,??source_stmt?????select*fromt1,??destination_stmtselect*fromt2,??validate????????false);end;/ERRORatline1:ORA-01031:insufficientprivilegesORA-06512:atSYS.DBMS_ADVANCED_REWRITE,line29ORA-06512:atSYS.DBMS_ADVANCED_REWRITE,line185ORA-06512:atline2SQLconn/assysdbaSQLgrantcreatematerializedviewtotom;Grantsucceeded.SQLconntom/tomSQLbegin?SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE(??name????????????TOM.SQL1,??source_stmt?????select*fromt1,??destination_stmtselect*fromt2,??validate????????false);end;/?2???3???4???5???6???7???8?PL/SQLproceduresuccessfullycompleted.3.5看效果SQLselect*fromt1;????????X----------????????1????????1--需要设置查询重写完整性SQLaltersessionsetquery_rewrite_integritytrusted;Sessionaltered.SQLselect*fromt1;norowsselected--变为大写不妨碍重写SQLselect*fromT1;norowsselected--增加空格不妨碍重写SQLselect*from?T1;norowsselected--增加别名影响重写SQLselect*fromt1a;????????X----------????????1????????1?来自“ITPUB博客”链接http://blog.itpub.net/18922393/viewspace-1247732/如需转载请注明出处否则将追究法律责任。转载于:http://blog.itpub.net/18922393/viewspace-1247732/点赞评论
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则