缺失合理索引导致SQL效率低下案例

近日同事在梳理生产系统TOP SQL时发现有一个SQL消耗了很多资源,情况如下:

SQL统计信息

SQL文本如下:

SELECT t.ID, t.FLT_OC_FLIGHT_ID, t.FLT_AIRLINE_CODE, t.FLT_FLIGHT_NO, t.FLT_FLIGHT_DATE
	, t.FLT_ALL_STATIONS, t.FLT_UPDATE_AGENT, t.FLT_UPDATE_TIME, t.FLT_NIGHT_FLIGHT_FLAG, t.FLT_INTERNATIONAL
	, t.FLT_ORI_STATION_CODE, t.FLT_DES_STATION_CODE, t.SEG_DATA_SOURCE, t.IS_VALID, t.INSERT_TIME
	, t.UPDATE_TIME, t.FLT_MSG_TID
FROM T_DEP_FDC_FLIGHT t
	INNER JOIN T_DEP_FDC_STATION s ON t.ID = s.STA_FLIGHT_ID 
WHERE t.FLT_AIRLINE_CODE IN (:1, :2, :3, :4)
	AND instr(t.FLT_ALL_STATIONS, :5) BETWEEN 1 AND length(t.FLT_ALL_STATIONS) - 3
	AND s.STA_STATION_CODE = :6
	AND s.STA_SCH_DEP_TIME <= :7
	AND s.STA_SCH_DEP_TIME >= :8
	AND t.IS_VALID = 1
	AND t.FLT_OC_FLIGHT_ID IS NULL

因为该SQL执行时间超过5s,Oracle会自动monitor,我们可以得到该SQL的SQL_MONITOR

从该条SQL的执行计划来看,T_DEP_FDC_FLIGHT 与T_DEP_FDC_STATION采用了Nested loop的方式进行JOIN,并且 T_DEP_FDC_FLIGHT 为驱动表。但是被驱动表扫描索引678K后,回表再过滤只剩下58行记录,并且 T_DEP_FDC_STATION 谓词条件的数据分布均匀,那么我们完全可以创建合适的索引,使得 T_DEP_FDC_STATION 表驱动 T_DEP_FDC_FLIGHT (小结果集驱动大结果集)。

模拟SQL:

select t.*
  from a t
 inner join s
    on t.object_id = s.object_id
 where t.NAMESPACE in (1, 24, 9, 64)
   and instr(t.LAST_DDL_TIME, '2') between 0 and
       length(t.LAST_DDL_TIME) - 3
   and s.NAMESPACE >= 23
   and s.NAMESPACE <= 25
   and s.status = 'VALID'
   and t.owner='SYS'
   and t.OBJECT_NAME is not null

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |  4640 (100)|          |     19 |00:00:03.66 |   18746 |
|   1 |  NESTED LOOPS                |         |      1 |     26 |  4640   (1)| 00:00:56 |     19 |00:00:03.66 |   18746 |
|   2 |   NESTED LOOPS               |         |      1 |    135 |  4640   (1)| 00:00:56 |  69487 |00:00:03.62 |   17632 |
|*  3 |    TABLE ACCESS FULL         | A       |      1 |    135 |  4497   (1)| 00:00:54 |   1076K|00:00:02.83 |   16476 |
|*  4 |    INDEX RANGE SCAN          | S_IDX01 |   1076K|      1 |     1   (0)| 00:00:01 |  69487 |00:00:00.57 |    1156 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| S       |  69487 |      1 |     2   (0)| 00:00:01 |     19 |00:00:00.03 |    1114 |
--------------------------------------------------------------------------------------------------------------------------

创建索引:
SQL> create index s_idx01 on s(status,NAMESPACE);

Index created.


select t.*
  from a t
 inner join s
    on t.object_id = s.object_id
 where t.NAMESPACE in (1, 24, 9, 64)
   and instr(t.LAST_DDL_TIME, '2') between 0 and
       length(t.LAST_DDL_TIME) - 3
   and s.NAMESPACE >= 23
   and s.NAMESPACE <= 25
   and s.status = 'VALID'
   and t.owner='SYS'
   and t.OBJECT_NAME is not null


Plan hash value: 3503728021
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |    84 (100)|          |     35 |00:00:00.04 |      64 |      1 |
|   1 |  NESTED LOOPS                 |         |      1 |      5 |    84   (0)| 00:00:02 |     35 |00:00:00.04 |      64 |      1 |
|   2 |   NESTED LOOPS                |         |      1 |     83 |    84   (0)| 00:00:02 |     50 |00:00:00.03 |      52 |      1 |
|   3 |    TABLE ACCESS BY INDEX ROWID| S       |      1 |     83 |     4   (0)| 00:00:01 |     54 |00:00:00.03 |      21 |      1 |
|*  4 |     INDEX RANGE SCAN          | S_IDX01 |      1 |     83 |     2   (0)| 00:00:01 |     54 |00:00:00.03 |       5 |      1 |
|*  5 |    INDEX UNIQUE SCAN          | A_PK1   |     54 |      1 |     0   (0)|          |     50 |00:00:00.01 |      31 |      0 |
|*  6 |   TABLE ACCESS BY INDEX ROWID | A       |     50 |      1 |     1   (0)| 00:00:01 |     35 |00:00:00.01 |      12 |      0 |
------------------------------------------------------------------------------------------------------------------------------------