执行一个语句耗时很长，为啥？

Question

现在有一张tbl_fm_tmp_event_log表，包含32个字段，分别在i_serial_no，DT_GEN_TIME字段上建了索引，并且表中有十万多条记录，
在sqlplus中，执行以下语句竟然要耗时几十秒钟
DELETE FROM cgp_1.tbl_fm_tmp_event_log WHERE i_serial_no IN(SELECT i_serial_no FROM (SELECT i_serial_no FROM cgp_1.tbl_fm_tmp_event_log ORDER BY DT_GEN_TIME)
WHERE ROWNUM <= 1);
但是我单独执行SELECT i_serial_no FROM (SELECT i_serial_no FROM cgp_1.tbl_fm_tmp_event_log ORDER BY DT_GEN_TIME) WHERE ROWNUM <= 1 或DELETE FROM cgp_1.tbl_fm_tmp_event_log WHERE ROWNUM <= 1; 都很快。
不知道为什么？

Answer 1

还比较奇怪的是，我有另一张表tbl_fm_tmp_alarm_log，表结构与前面那个tbl_fm_tmp_event_log是一样的，只是在I_SYNC_NO和DT_GEN_TIME上分别建了索引，记录也是一样的十万多条，但执行上面类似语句却非常快：
DELETE FROM cgp_1.tbl_fm_tmp_alarm_log WHERE I_SYNC_NO IN(                                     
SELECT I_SYNC_NO FROM (SELECT I_SYNC_NO FROM cgp_1.tbl_fm_tmp_alarm_log ORDER BY DT_GEN_TIME)
WHERE ROWNUM <= 6000);我把autotrace信息抓下来发现两个执行有些许不同，但只晓得怎么会导致如此大的差异，即便是全表扫描也不该那么慢啊。第一条语句：FM_INDEX_TMP_EVENT_LOG_SERIAL就是建在i_serial_no上的索引
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT                 |                               |     1 |    26 |     0   (0)| 00:00:01 |
|   1 |  DELETE                          | TBL_FM_TMP_EVENT_LOG          |       |       |            |          |
|*  2 |   INDEX FULL SCAN                | FM_INDEX_TMP_EVENT_LOG_SERIAL |     1 |    26 |     0   (0)| 00:00:01 |
|*  3 |    FILTER                        |                               |       |       |            |          |
|*  4 |     COUNT STOPKEY                |                               |       |       |            |          |
|   5 |      VIEW                        |                               |     1 |    13 |     0   (0)| 00:00:01 |
|   6 |       TABLE ACCESS BY INDEX ROWID| TBL_FM_TMP_EVENT_LOG          |     1 |    26 |     0   (0)| 00:00:01 |
|   7 |        INDEX FULL SCAN           | FM_INDEX_TMP_EVENT_LOG        |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
---------------------------------------------------   2 - filter( EXISTS (<not feasible>)
   3 - filter("I_SERIAL_NO"=:B1)
   4 - filter(ROWNUM<=1)

Answer 2

第二条语句：FM_INDEX_SYNC_TMP_ALARM_LOG 就是建在I_SYNC_NO 上的索引
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT                 |                             |     1 |    39 |     3  (34)| 00:00:01 |
|   1 |  DELETE                          | TBL_FM_TMP_ALARM_LOG        |       |       |            |          |
|*  2 |   HASH JOIN SEMI                 |                             |     1 |    39 |     3  (34)| 00:00:01 |
|   3 |    INDEX FULL SCAN               | FM_INDEX_SYNC_TMP_ALARM_LOG |     1 |    26 |     0   (0)| 00:00:01 |
|   4 |    VIEW                          | VW_NSO_1                    |     1 |    13 |     2   (0)| 00:00:01 |
|*  5 |     COUNT STOPKEY                |                             |       |       |            |          |
|   6 |      VIEW                        |                             |     1 |    13 |     2   (0)| 00:00:01 |
|   7 |       TABLE ACCESS BY INDEX ROWID| TBL_FM_TMP_ALARM_LOG        |     1 |    26 |     2   (0)| 00:00:01 |
|   8 |        INDEX FULL SCAN           | FM_INDEX_TMP_ALARM_LOG      |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
---------------------------------------------------   2 - access("I_SYNC_NO"="I_SYNC_NO")
   5 - filter(ROWNUM<=6000)执行快的那个多了|   HASH JOIN SEMI                 |和
|    VIEW                          | VW_NSO_1                    |
这两句，不知道是什么意思，高手给解答一下吧，谢谢啦！

Answer 3

csdn的排版实在是让我郁闷！
明明整整齐齐的发上来就变成这个鬼样儿了。

Answer 4

对表做个统计试试吧,exec dbms_stats.gather_table_stats(user, 表名)

Answer 5

DT_GEN_TIME, i_serial_no这个2字段建个复合索引试试

Answer 6

(1)DELETE FROM cgp_1.tbl_fm_tmp_event_log 
WHERE i_serial_no IN(
SELECT i_serial_no 
FROM (SELECT i_serial_no FROM cgp_1.tbl_fm_tmp_event_log ORDER BY DT_GEN_TIME) 
WHERE ROWNUM <= 1); 上面这个语句是不能和下面两个语句比较的;
(2)SELECT i_serial_no FROM (SELECT i_serial_no FROM cgp_1.tbl_fm_tmp_event_log ORDER BY DT_GEN_TIME) WHERE ROWNUM <= 1 
(3)DELETE FROM cgp_1.tbl_fm_tmp_event_log WHERE ROWNUM <= 1语句(1)含有两个子查询,并且是删除操作;