近日同事在梳理生产系统TOP SQL时发现有一个SQL消耗了很多资源，情况如下：
![](http://albertdba.com/"wp-content/uploads"/2020/07/20200712-1-1024x75.png)
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
![](http://albertdba.com/"wp-content/uploads"/2020/07/20200712-2-1024x348.png)
从该条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 |
------------------------------------------------------------------------------------------------------------------------------------