请问如下两条SQL语句为什么执行时间差这么多？

Question

语句-1  select rawdata.*, rownum rownum_ from ( 
    select t1.train_no, t1.train_id, t1.err_code, t1.vehicle_order, t1.sub_system,        
           t1.tm_error, t1.err_status, t1.err_mode, t1.err_type, t2.event  
    from tbl_error t1, tbl_error_lib t2  
    where t1.err_code=t2.code and t1.train_id='224' and t1.train_no=13  
          and t1.tm_error > (( select max(t3.tm_error) from tbl_error t3) - 24*60*60)
    group by t1.train_no, t1.train_id, t1.err_code, t1.sub_system, t1.err_type,           
             t1.vehicle_order, t1.tm_error, t1.err_status, t1.err_mode, t2.event
    order by t1.tm_error desc
  ) rawdata 
  where rownum < 100 
语句-2select * from ( 
  select rawdata.*, rownum rownum_ from ( 
    select t1.train_no, t1.train_id, t1.err_code, t1.vehicle_order, t1.sub_system,        
           t1.tm_error, t1.err_status, t1.err_mode, t1.err_type, t2.event  
    from tbl_error t1, tbl_error_lib t2  
    where t1.err_code=t2.code  and t1.train_id='224'  and t1.train_no=13 
          and t1.tm_error > 1257436800 and t1.tm_error < 1257519600 
    group by t1.train_no, t1.train_id, t1.err_code, t1.sub_system, t1.err_type,   
             t1.vehicle_order, t1.tm_error, t1.err_status, t1.err_mode, t2.event  
    order by t1.tm_error desc 
  ) rawdata 
  where rownum < 41 
) 
where rownum_ >= 1
语句-1在PLSQL中看到执行时间为6.515秒，语句2为0.53秒。二者查询的同样的表。
该表有两个索引，分别是包含列TRAIN_NO, TRAIN_ID, TM_ERROR, VEHICLE_ORDER, ERR_CODE, ERR_TYPE的组合索引，和列TM_ERROR的倒序索引（DESC）。

Answer 1

比较慢的那个SQL的执行计划如下：SELECT STATEMENT, GOAL = ALL_ROWS Cost=940 Cardinality=1 Bytes=1234 IO cost=902 CPU cost=220314692 Time=12
 COUNT STOPKEY
  VIEW Object owner=RECVSVR Cost=7 Cardinality=1 Bytes=1234 IO cost=6 CPU cost=5864728 Time=1
   SORT ORDER BY Cost=7 Cardinality=1 Bytes=122 IO cost=6 CPU cost=5864728 Time=1
    HASH GROUP BY Cost=7 Cardinality=1 Bytes=122 IO cost=6 CPU cost=5864728 Time=1
     TABLE ACCESS BY INDEX ROWID Object owner=RECVSVR Object name=TBL_ERROR_LIB Cost=2 Cardinality=1 Bytes=47 IO cost=2 CPU cost=15913 Time=1
      NESTED LOOPS Cost=6 Cardinality=1 Bytes=122 IO cost=6 CPU cost=45029 Time=1
       TABLE ACCESS BY INDEX ROWID Object owner=RECVSVR Object name=TBL_ERROR Cost=4 Cardinality=1 Bytes=75 IO cost=4 CPU cost=29116 Time=1
        INDEX RANGE SCAN DESCENDING Object owner=RECVSVR Object name=ERROR_INDEX Cost=3 Cardinality=1 IO cost=3 CPU cost=21564 Time=1
       INDEX RANGE SCAN Object owner=RECVSVR Object name=INDEX_ERROR_LIB Cost=1 Cardinality=1 IO cost=1 CPU cost=8371 Time=1
   SORT AGGREGATE Cardinality=1 Bytes=7
    INDEX FAST FULL SCAN Object owner=RECVSVR Object name=IDX_TMERR_DESC Cost=933 Cardinality=1544598 Bytes=10812186 IO cost=896 CPU cost=214449964 Time=12

Answer 2

比较快的那个SQL的执行计划SELECT STATEMENT, GOAL = ALL_ROWS Cost=7 Cardinality=1 Bytes=1247 IO cost=6 CPU cost=5864728 Time=1
 VIEW Object owner=RECVSVR Cost=7 Cardinality=1 Bytes=1247 IO cost=6 CPU cost=5864728 Time=1
  COUNT STOPKEY
   VIEW Object owner=RECVSVR Cost=7 Cardinality=1 Bytes=1234 IO cost=6 CPU cost=5864728 Time=1
    SORT ORDER BY STOPKEY Cost=7 Cardinality=1 Bytes=122 IO cost=6 CPU cost=5864728 Time=1
     HASH GROUP BY Cost=7 Cardinality=1 Bytes=122 IO cost=6 CPU cost=5864728 Time=1
      TABLE ACCESS BY INDEX ROWID Object owner=RECVSVR Object name=TBL_ERROR_LIB Cost=2 Cardinality=1 Bytes=47 IO cost=2 CPU cost=15913 Time=1
       NESTED LOOPS Cost=6 Cardinality=1 Bytes=122 IO cost=6 CPU cost=45029 Time=1
        TABLE ACCESS BY INDEX ROWID Object owner=RECVSVR Object name=TBL_ERROR Cost=4 Cardinality=1 Bytes=75 IO cost=4 CPU cost=29116 Time=1
         INDEX RANGE SCAN DESCENDING Object owner=RECVSVR Object name=ERROR_INDEX Cost=3 Cardinality=1 IO cost=3 CPU cost=21564 Time=1
        INDEX RANGE SCAN Object owner=RECVSVR Object name=INDEX_ERROR_LIB Cost=1 Cardinality=1 IO cost=1 CPU cost=8371 Time=1

Answer 3

看COST中占用,
SORT AGGREGATE Cardinality=1 Bytes=7 
    INDEX FAST FULL SCAN Object owner=RECVSVR Object name=IDX_TMERR_DESC Cost=933 Cardinality=1544598 Bytes=10812186 IO cost=896 CPU cost=214449964 Time=12 
全文索引

Answer 4

INDEX FAST FULL SCAN Object owner=RECVSVR Object name=IDX_TMERR_DESC Cost=933 Cardinality=1544598 Bytes=10812186 IO cost=896 CPU cost=214449964 Time=12
第一个查询多了这一步，大大降低了性能
你查下索引，select table_name,column_name from all_ind_columns where index_name='IDX_TMERR_DESC'应该是在这一步上and t1.tm_error > (( select max(t3.tm_error) from tbl_error t3) - 24*60*60)

Answer 5

多谢老师提醒，是select max(t3.tm_error) from tbl_error t3 这句话造成的，全文索引，这里是希望取出最近24小时内更新的数据，以此避免对整个记录集的order by，还有什么好办法呢？

Answer 6

另外，单独执行select max(t3.tm_error) from tbl_error t3这一句的话，执行计划确实是全文索引扫描，但是执行时间只有0.329秒。不知道是不是由于Oracle的缓存 ？ （因为在单独执行这一句之前，执行过上述完整的SQL 语句）