SQL优化:理解SQL的意图

　　如果你的系统中，通过statspack report或awr report，发现这样的SQL执行比较频繁，而且test表的记录数比较多，test表中附合status = '1'的记录数也非常多，占test表记录数90%的比例吧，从statspack report或awr report可以发现该SQL的逻辑读会相当大，假定该表没有任何索引，那DBA该如何着手进行SQL的优化呢?

　　SELECTseller_id
　　FROM(SELECT*FROM(SELECTseller_id,max(gmt_create)FROMtestWHEREstatus=1GROUPBYseller_id)
　　ORDERBYgmt_createDESC)
　　WHERErownum<=10;

　　方案A

　　假如test表需要存储空间4g，在test表的(status,gmt_create,sellerid)字段上创建组合索引需要存储空间1g，那在test表(status,gmt_create,sellerid)上创建索引，确实可以有效地提高SQL的性能，因为从全表扫描转换成快速索引全扫描，需要扫描的索引块数虽然远远少于全表扫描时的数据块数，但实际上需要计算的记录数并没有减少，逻辑读还是相当大。

　　SQL>explainplanforSELECTseller_id
　　2FROM(SELECT*
　　3FROM(SELECTseller_id,max(gmt_create)ASgmt_create
　　4FROMtest
　　5WHEREstatus=1
　　6GROUPBYseller_id)
　　7ORDERBYgmt_createDESC)
　　8WHERErownum<=10;
　　SQL>select*fromtable(dbms_xplan.display);
　　PLAN_TABLE_OUTPUT
　　-----------------------------------------------------------------------
　　|Id|Operation|Name|Rows|Bytes|Cost|
　　-----------------------------------------------------------------------
　　|0|SELECTSTATEMENT||10|180|7916|
　　|*1|COUNTSTOPKEY|||||
　　|2|VIEW||14194|249K|7916|
　　|*3|SORTORDERBYSTOPKEY||14194|609K|7916|
　　|4|SORTGROUPBY||14194|609K|7916|
　　|*5|INDEXFASTFULLSCAN|IDX_TEST_STATUS|849K|35M|1066|
　　-----------------------------------------------------------------------

　　方案B

　　那我们该如何进一步进行优化呢?现在需要分析一下该SQL的意图，只有明白了SQL的意图，才可以找到更有效的优化方法：该SQL最内层的意图很明显，就是把记录按seller_id进行分组，并按gmt_create进行逆向排序;外层意图就更简单啦，取创建时间最新的10条记录。即然是取最新的10条记录，那我们就可以把该SQL演化一下：

　　SQL>explainplanforSELECTseller_id
　　2FROM(SELECT*
　　3FROM(SELECTseller_id,max(gmt_create)ASgmt_create
　　4FROMtest
　　5WHEREstatus=1
　　6ANDgmt_create>=sysdate-1/24
　　7GROUPBYseller_id)
　　8ORDERBYgmt_createDESC)
　　9WHERErownum<=10;
　　SQL>select*fromtable(dbms_xplan.display);
　　---------------------------------------------------------------------
　　|Id|Operation|Name|Rows|Bytes|Cost|
　　---------------------------------------------------------------------
　　|0|SELECTSTATEMENT||10|180|495|
　　|*1|COUNTSTOPKEY|||||
　　|2|VIEW||13523|237K|495|
　　|*3|SORTORDERBYSTOPKEY||13523|581K|495|
　　|4|SORTGROUPBY||13523|581K|495|
　　|*5|INDEXRANGESCAN|IDX_TEST_STATUS|42482|1825K|78|
　　---------------------------------------------------------------------

　　说明：即然是取最新的10记录，其实是没有必要把所有的数据都group by一次的，所以我们可以取最近一小时的数据，做group by就可以满足需求，如果增量数据很大，也可以考虑取走分钟(5/1440)的增量数据等，通过查询条件gmt_create >= sysdate - 1/24，把许多不需要进行group by的记录都排除了。

　　也就是说，进行SQL调优时，理解SQL的意图很重要。