一个SQL COUNT 优化案例,作一个小结[@more@]
SQL需求:在一个8000W的表中,找出修改时间为某一时间段的记录,并且某字段不为空:
select count(*)
from aliim.udb_user_profile a
where a.gmt_modified >= to_date('2008-07-21 14:29:38', 'yyyy-mm-dd hh24:mi:ss')
and a.gmt_modified <= to_date('2008-07-21 15:34:50', 'yyyy-mm-dd hh24:mi:ss')
and a.AEP_USER_ID is not null
首先想到的是在 gmt_modified 字段中创建单列索引,让查询走索引,并返回到表中过滤 a.AEP_USER_ID is not null.
create index udb_user_profile_gmtm_ind on udb_user_profile(gmt_modified ) online compute statistics ;
在测试库,看执行计划:
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 75 | 6 |
| 1 | SORT AGGREGATE | | 1 | 75 | |
|* 2 | TABLE ACCESS BY INDEX ROWID| UDB_USER_PROFILE | 481 | 36075 | 6 |
|* 3 | INDEX RANGE SCAN | UDB_USER_PROFILE_GMTM_IND | 17308 | | 2 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."AEP_USER_ID" IS NOT NULL)
3 - access("A"."GMT_MODIFIED">=TO_DATE('2008-07-20 14:29:38', 'yyyy-mm-dd hh24:mi:ss')
AND "A"."GMT_MODIFIED"<=TO_DATE('2008-07-21 15:34:50', 'yyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
27 consistent gets
18 physical reads
0 redo size
379 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
结果比较理想,但到了产品库中,相同的执行计划,却发现这条SQL执行了40多分钟.......
原因是因为生产库中所能选择到的数据远比测试库要多得多(10W以上).
我们作了分析:
oracle先走索引:UDB_USER_PROFILE_GMTM_IND ,找到满足条件的ROWID后(如果是100000条,至少是300个块),那么下一步就去表中找到这100000条记录(最坏的情况是这100000记录都分散在不同的块,那么这一次的读将会是100000个),最后作filter("A"."AEP_USER_ID" IS NOT NULL)并返回实际满足条件的COUNT值。
初想这个执行计划没有啥大问题,而且也走得似乎很合情合理,但就是时间太长了,,,,,
下面是高手出马的结果:
从 GMT_MODIFIED 列来看,我们没有更好的办法来优化,那么我们来看一下AEP_USER_ID这个列中有没有文章可做:
我们来看一下AEP_USER_ID的数据分布:
-----------------
NOT NULL : 5000000
NULL : 75000000
我们知道,ORACLE的索引是不存储为NULL的数据的,因为NOTNULL的数据比较少,所以索引也相对比较小。 如果让COUNT不访问表而直接访问索引,那将会是一个比较理想的路径,于是就大胆的作了测试:
create index udb_user_profile_auid_ind on udb_user_profile(AEP_USER_ID) online compute statistics;
再将SQL改成如下形式:
select /*+ordered use_hash(a b) index_ffs(b UDB_USER_PROFILE_AUID_IND)*/
count(*)
from aliim.udb_user_profile a, aliim.udb_user_profile b
where a.rowid = b.rowid
and a.gmt_modified >=
to_date('2008-07-20 14:29:38', 'yyyy-mm-dd hh24:mi:ss')
and a.gmt_modified <=
to_date('2008-07-21 15:34:50', 'yyyy-mm-dd hh24:mi:ss')
and b.AEP_USER_ID is not null
看执行计划:
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 7 |
| 1 | SORT AGGREGATE | | 1 | 22 | |
|* 2 | HASH JOIN | | 1 | 22 | 7 |
|* 3 | INDEX RANGE SCAN | UDB_USER_PROFILE_GMTM_IND | 2 | 28 | 4 | (直接走index即可)
|* 4 | INDEX FAST FULL SCAN| UDB_USER_PROFILE_AUID_IND | 1 | 8 | 2 |
-------------------------------------------------------------------------------------
oracle只走了两个索引,将对两个索引作了HASH连接,没有回表。
当完全配置后,运行这个SQL只用了不到1分钟的时间。
优化过程中,主要原理还是让ORACLE去访问更少的数据块。
当然这种方法还是有一定的风险:
当b.AEP_USER_ID 的非空值越来越大时,那么访问UDB_USER_PROFILE_AUID_IND这个索引的时间也会越来越长,到时候这个SQL的执行计划是不是还有那么优越,就要视情况而定了!
最后,这确实是一种优化的好思路!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/703656/viewspace-1007653/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/703656/viewspace-1007653/