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

[neverland_83]

现在有一张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; 都很快。
不知道为什么？

[neverland_83]

还比较奇怪的是，我有另一张表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)

[neverland_83]

第二条语句：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 |
这两句，不知道是什么意思，高手给解答一下吧，谢谢啦！

[neverland_83]

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

[zcs_1]

对表做个统计试试吧,

exec dbms_stats.gather_table_stats(user, 表名)

[codearts]

DT_GEN_TIME, i_serial_no

这个2字段建个复合索引试试

[qwx312347236]

学习

[Andy__Huang]

(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)含有两个子查询,并且是删除操作;