SQL最佳化-COUNT_ INDEX的巧用

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的執行計劃是不是還有那麼優越，就要視情況而定了！

最後，這確實是一種最佳化的好思路！