索引的创建直接关系到执行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系统,索引可能会根据需求不同改变或者重建。