资源描述：
《sql中使用withas提高性能》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。
1、摘要：本文结合笔者实际的工作情况，说明了如何使用withas改写存储过程，从而大幅提高SQL的运行时间。本文首先交代了案例的起因，随后简单介绍了WITHAS的含义，最后基于实际工作，使用了一个测试用例来说明如何使用WITHAS。1.案例起因 公司门店应用程序每天都要出一份报表，用来统计所有商品当天的期初库存数量、入库数量、出库数量及当天的期末库存数量。运行半年以后，这份报表运行越来越慢，到现在，每次运行该报表显示当天数据时需要近20秒的时间。于是开发人员找到我，希望我看看，是不是可以使该报表运行的时间更短。该报表就是一段SQL语句，主要由三部分组成，第
2、一部分是计算每个商品的期初数量，第二部分是计算每个商品的当天发生（包括入库和出库的）数量，第三部分是计算每个商品的期末数量，也就是当天的余额。每个部分使用UNIONALL连接起来。我看到该报表，第一个感觉就是这段SQL里的每个部分都要对表进行扫描，明显成本过高。应该可以使用WITHAS进行改写。2.WITHAS的含义 WITHAS短语，也叫做子查询部分（subqueryfactoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNIONALL的不同部分，作
3、为提供数据的部分。特别对于UNIONALL比较有用。因为UNIONALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用WITHAS短语，则只要执行一遍即可。如果WITHAS短语所定义的表名被调用两次以上，则优化器会自动将WITHAS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。而提示materialize则是强制将WITHAS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。3.案例说明 首先介绍该SQL所涉及到的主要的表的结构。该表表名为fin，用来存放每天每个商品的发生数以及该商品
4、的余额数。其表结构为如下所示（这里我只选取了与我们要讨论的SQL相关的部分表字段）。SQL>descfin名称是否为空?类型-----------------------------------------------------------------------------。。。。。。DAYDATESKUVARCHAR2(8)INQTYNUMBER(16,6)OUTQTYNUMBER(16,6)LASTQTYNUMBER(16,6)。。。。。。。。简单解释一下各个字段的含义：1）DAY：发生的日期。2）SKU：发生交易的商品代码。3）INQTY：商品入
5、库数量。4）OUTQTY：商品出库数量。5）LASTQTY：商品的余额数量。该表中含有的记录数量为：SQL>SELECTcount(*)FROMfin;COUNT(*)----------4729319原来的SQL如下所示（比如查询2003年7月14日这天的记录。当然，我对该SQL做了些修改，去掉了与本文讨论无关的部分，比如显示商品名称之类的部分等）：SELECTsku,sum(initqty)asinitqty,sum(inqty)asinqty,sum(outqty)asoutqty,sum(lastqty)aslastqtyFROM(SELECTsk
6、u,lastqtyasinitqty,0asinqty,0asoutqty,0aslastqtyFROMfinWHEREday=to_date('20030713','yyyymmdd')UNIONALLSELECTsku,0asinitqty,inqty,outqty,0aslastqtyFROMfinWHEREday>=to_date('20030714','yyyymmdd')andday<=to_date('20030714','yyyymmdd')UNIONALLSELECTsku,0asinitqty,0asinqty,0asoutqty,la
7、stqtyFROMfinWHEREday=to_date('20030714','yyyymmdd'))GROUPBYsku;我们来看该SQL所花费的时间为：SQL>settimingonSQL>/。。。。。。。。。。。。。。。。。。。。。。。。。。。。。SKUINITQTYINQTYOUTQTYLASTQTY------------------------------------------------00106162001260001064670201060已选择956行。已用时间:00:00:19.08然后，我们来对该SQL进行改写一番，如下所示：W
8、ITHresultAS(SELECT/*+materialize*
