怎样避免如下SQL语句中的全表扫描？

Question

SQL语句
select t.code, t.value from tbl_comm_device t where t.data_time = ( 
  select max(tt.data_time) from tbl_comm_device tt where tt.src_id='224' and tt.code='10'
)
表结构：create table TBL_COMM_DEVICE
(
  ID          NUMBER not null,
  SRC_ID      NUMBER not null,
  TRAIN_NO    NUMBER not null,
  TRAIN_ID    NCHAR(8) not null,
  CODE        NCHAR(10),
  VALUE       NCHAR(256),
  DATA_TIME   NUMBER,
  UPDATE_TIME DATE default sysdate not null
)create index IDX_QUERY on TBL_COMM_DEVICE (CODE, DATA_TIME, SRC_ID, TRAIN_NO)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index IDX_SRCID on TBL_COMM_DEVICE (SRC_ID)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
执行计划：SELECT STATEMENT, GOAL = ALL_ROWS Cost=18 Cardinality=2 Bytes=1076 IO cost=18 CPU cost=733786 Time=1
 TABLE ACCESS FULL Object owner=RECVSVR Object name=TBL_COMM_DEVICE Cost=16 Cardinality=2 Bytes=1076 IO cost=16 CPU cost=709294 Time=1
  SORT AGGREGATE Cardinality=1 Bytes=29
   INDEX RANGE SCAN Object owner=RECVSVR Object name=IDX_QUERY Cost=2 Cardinality=20 Bytes=580 IO cost=2 CPU cost=24493 Time=1现在表中数据很少，也就不到1000条，看不出性能问题，但以后数据多会很慢啊~~ 奇怪的是我已经根据SQL的条件建了组合索引为什么还会有全表扫描？

Answer 1

select t.code, t.value from tbl_comm_device t where t.src_id='224' and t.code='10'
and rownum =1
order by data_time desc

Answer 2

写错啦：
这样select * from (select t.code, t.value from tbl_comm_device t where t.src_id='224' and t.code='10' 
 
order by data_time desc )
where rownum =1

Answer 3

数据多了以后order by是很慢么？

Answer 4

你在data_time 建索引没？建啦应该不慢嘛

Answer 5

--SRC_ID是NUMBER类型的，tt.src_id = '224'这样写是不合理的, FYI:
SELECT T1.CODE, T1.VALUE
  FROM TBL_COMM_DEVICE T1
 WHERE NOT EXISTS (SELECT 1
          FROM TBL_COMM_DEVICE T2
         WHERE T1.SRC_ID = T2.SRC_ID
           AND T1.CODE = T2.CODE
           AND T1.DATA_TIME < T2.DATA_TIME)
   AND T1.SRC_ID = 224
   AND T1.CODE = '10';

Answer 6

我晕用NOT EXISTS ？？？
效率更低，还不如全表呢

Answer 7

  先对data_time建立索引,正解   

Answer 8

order by操作是最伤COST，LZ可以拿NOT EXISTS和上面ORDER BY的SQL语句进行数据压力测试，看看便知效率。至于data_time字段，个人觉得没必要建索引。

Answer 9

如果建立了合适的索引却在执行计划上没能用上只有一个可能性:oracle优化器认为在当前的资源条件下,执行全表扫描有更低的成本.
而成本的构成在9i以上的优化器中除了传统的IO成本,还加上了CPU成本的计算,因而计划选择可变因素更复杂.
所有成本的计算以数据字典及动态性能视图中的统计数据为基础,因此统计数据的真实性(或实时性-在数据仓库的环境下)显得尤为重要.
因此,如果没有对架构对象进行适当采样的分析,10g以上没有对IO能力及CPU能力在典型负载期间进行收集,并在适当的间隔内更新过期的分析数据,执行计划就有可能偏离你预期的目标而实现.
如果你清楚地知道你所查询数据的统计特性,实际上你也可以避开优化器的选择,而直接通过加入优化提示的方法去实现你的目标.但是此方法不推荐使用,因为你的选择也可能随着时间的推移而不再适合.但在解决孤立的性能问题时,作为应急办法确实比较有效.

Answer 10

首先楼主先要认识个理解误区，就是全表扫描未必是最慢的
走索引快是基于查询量占总量不大的情况
如果查询量占全表的百分比很大，CBO会自动选择全表扫描的

Answer 11

SELECT t.code, t.VALUE FROM (
select t.code, t.value,SUM(1) over (ORDER BY t.datatime DESC) cid  
from tbl_comm_device t where  t.src_id='224' and t.code='10'
) WHERE cid=1