某条SQL语句造成大量IO等待，请问该如何优化？

Question

SQL语句：
SELECT * FROM ( SELECT TRAIN_NO, TRAIN_ID, UPDATE_TIME, INFO_DATA FROM TBL_TIMER_INFO WHERE TRAIN_ID='225' order by UPDATE_TIME desc ) WHERE ROWNUM < 2 
上述列中，INFO_DATA是NCLOB类型，目前该表内约有3w多条数据，使用命令
select segment_name,bytes/1024/1024 from user_segments;查得的表大小为280MB，Oracle企业管理器提供的信息如下：
操作 调查涉及 TABLE "RECVSVR.TBL_TIMER_INFO" (对象 ID 为 52656) 的 I/O 的应用程序逻辑。  
     数据库对象RECVSVR.TBL_TIMER_INFO 
操作 在 TABLE "RECVSVR.TBL_TIMER_INFO" (对象 ID 为 52656) 上运行 "Segment Advisor"。 
     数据库对象RECVSVR.TBL_TIMER_INFO 
 
原理 对象的 I/O 使用统计信息为: 60 完整对象扫描, 2122410 物理读取, 0 物理写入和 0 直接读取。 
原理 SQL_ID 为 "azvxr5a8fxwym" 的 SQL 语句在等待热对象的用户 I/O 上消耗了大量时间。 此外该表在UPDATE_TIME列上建有索引，该列默认值是sysdate。我是一Oracle菜鸟，承蒙各位老大多多帮助。

Answer 1

有fts么。看一下执行过程先。

Answer 2

执行计划如下：
SELECT STATEMENT, GOAL = ALL_ROWS Cost=7836 Cardinality=1 Bytes=2042 IO cost=7789 CPU cost=275377719 Time=95
 COUNT STOPKEY
  VIEW                           Cost=7836 Cardinality=1 Bytes=2042 IO cost=7789 CPU cost=275377719 Time=95
   SORT ORDER BY STOPKEY          Cost=7836 Cardinality=1 Bytes=115  IO cost=7789 CPU cost=275377719 Time=95
    TABLE ACCESS FULL             Cost=7835 Cardinality=1 Bytes=115  IO cost=7789 CPU cost=269558020 Time=95

Answer 3

语句需要优化，大数据集尽量避免order by排序
改成这样再看看select * from(
  select t.*,row_number()over(order by t.UPDATE_TIME desc)rn
  from TBL_TIMER_INFO t
  where TRAIN_ID='225')
where rn<2

Answer 4

对UPDATE_TIME 字段和 TRAIN_ID建索引

Answer 5

加TRAIN_ID索引后应该提升不少，UPDATE_TIME已经有索引了

Answer 6

该加复合索引，on (train_id, update_time)

Answer 7

可是train_id这一列分离度很差，目前这一列只有两个不同的值。这样的列建索引效果好么？

Answer 8

需求是想求最近更新的一条记录吗？那就在UPDATE_TIME建一个索引，where中加上UPDATE_TIME=(select max(UPDATE_TIME) from xxx)

Answer 9

感谢各位！向大家汇报下优化的成果。 - 原始：原来的执行计划中 IO cost是7789， - 措施A：根据6楼codearts大哥的建议，在建立包括train_id列和update_time列的组合索引后，同样的SQL语句，IO cost变为186，Cardinality为1 - 措施B：根据8楼associate大哥的建议，进一步优化SQL语句后，IO cost变为5，但Cardinality值为36491。IO cost大幅度下降，但是该如何理解Cardinality值的变化？ 再次感谢！

Answer 10

不是很懂优化。为什么我的优化界面和你们的不一样啊执行计划
----------------------------------------------------------
Plan hash value: 264906180--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     5 |    90 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TT   |     5 |    90 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------Note
-----
   - dynamic sampling used for this statement
统计信息
----------------------------------------------------------
        169  recursive calls
          0  db block gets
         34  consistent gets
          0  physical reads
          0  redo size
        591  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          5  rows processed

Answer 11

   TABLE ACCESS FULL            Cost=7835 Cardinality=1 Bytes=115  IO cost=7789 CPU cost=269558020这里主要是有一个fts所以建立TRAIN_ID的index就可以了。Cardinality可以理解为离散度。