SQL优化-COUNT_ INDEX的巧用

一个SQL COUNT 优化案例，作一个小结

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的执行计划是不是还有那么优越，就要视情况而定了！

最后，这确实是一种优化的好思路！