如何创建合适的索引

 索引的创建直接关系到执行sql语句的好坏，下面举个实例：

SELECT SR_KPI_TYPE,
       SR_ASSESS_TYPE,
       SR_BSS_ORG_ID,
       SR_BSS_ORG_NAME,
       SR_CHANNEL_TYPE_ID,
       0                   GZ_HSL_AMOUNT_CUR,
       SR_YSJE_AMOUNT_AGE1 GZ_YSJE_AMOUNT_CUR,
       SR_SSJE_AMOUNT_AGE1 GZ_SSJE_AMOUNT_CUR,
       0                   GZ_HSL_AMOUNT_AGE1,
       SR_YSJE_AMOUNT_AGE2 GZ_YSJE_AMOUNT_AGE1,
       SR_SSJE_AMOUNT_AGE2 GZ_SSJE_AMOUNT_AGE1,
       0                   GZ_HSL_AMOUNT_LAST,
       SR_YSJE_AMOUNT_AGE3 GZ_YSJE_AMOUNT_LAST,
       SR_SSJE_AMOUNT_AGE3 GZ_SSJE_AMOUNT_LAST,
       STATEMENT_DATE,
       REMARK
FROM   RPT_KPI_SR_QD
WHERE  SR_CHANNEL_TYPE_ID = TRIM('1') AND
       SR_KPI_TYPE = TRIM('欠费回收') AND
       STATEMENT_DATE = 201112

此表接近700W数据
SQL> SELECT count(*) from  RPT_KPI_SR_QD;
 
  COUNT(*)
----------
   6990836
在之前的查询中语句需2个多小时才能出，该语句只是个查语句,存在6个存储过程中.
查看执行计划
--------------------
Plan hash value: 2366519870

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |  6993K|   433M|   157K  (1)| 00:31:30 |
|   1 |  TABLE ACCESS FULL| RPT_KPI_SR_QD |  6993K|   433M|   157K  (1)| 00:31:30 |
-----------------------------------------------------------------------------------

经过分析STATEMENT_DATE经常会作为单独查询使用，SR_CHANNEL_TYPE_ID和SR_KPI_TYPE会同时组合使用，所以考虑在STATEMENT_DATE上面创建一个索引，SR_CHANNEL_TYPE_ID和SR_KPI_TYPE上面创建组合索引。
再次查看查询情况：
Plan hash value: 3388422055

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |   146K|  7709K| 12459   (1)| 00:02:30 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| RPT_KPI_SR_QD       |   146K|  7709K| 12459   (1)| 00:02:30 |
|*  2 |   INDEX RANGE SCAN          | IDX_RPT_KPI_SR_QD_1 |   590K|       |  1821   (1)| 00:00:22 |
---------------------------------------------------------------------------------------------------

现在只需要3秒钟就可以出数据,索引的创建要根据业务逻辑来判断是否该建索引,应该创建哪些索引.

当此表作为关联表时，或许部分索引需要重建，所以作为OLTP系统，索引可能会根据需求不同改变或者重建。