为什么加上索引速度奇慢无比？

jimmylee

非常感谢halaer的精辟总结，我测试了一下count(stopkey)确实比count的效率要高，最起码不比count的效率低（当count(stopkey)计算所有的记录时），特别是当查询前多少条的时候（后多少条可以用order by ...desc解决)，其实说白了主要是对inline view的理解问题，

另外对于我的问题，无法用中间表解决，因为我是做一个监控，数据时刻在变化，实时性比较强，但中间表对于相对变化不太频繁的数据是一个不错的选择。

我的语句应该用下列语句取代：（索引和原来一样）
SELECT   a.ROWID, a.moni_time, a.moni_type, a.moni_aperrcode, a.moni_constant,
         SUBSTR (a.moni_userinfo, 1, 50) moni_userinfo
    FROM cstmonilog2 a,
         (SELECT rid
            FROM (SELECT ROWNUM rno, rid
                    FROM (SELECT   ROWID rid
                              FROM cstmonilog2
                             WHERE moni_area = '9'
                               AND moni_typeid = '1'
                               AND moni_flag = '1'
                          ORDER BY moni_time) where rownum<:EndNum) --changed
           WHERE rno >= :BeginNum) b --changed
   WHERE a.ROWID = b.rid
ORDER BY A.moni_time

(原来的语句是：
SELECT   a.ROWID, a.moni_time, a.moni_type, a.moni_aperrcode, a.moni_constant,
         SUBSTR (a.moni_userinfo, 1, 50) moni_userinfo
    FROM cstmonilog2 a,
         (SELECT rid
            FROM (SELECT ROWNUM rno, rid
                    FROM (SELECT   ROWID rid
                              FROM cstmonilog2
                             WHERE moni_area = '9'
                               AND moni_typeid = '1'
                               AND moni_flag = '1'
                          ORDER BY moni_time) )
           WHERE rno between :BeginNum and :EndNum) b
   WHERE a.ROWID = b.rid
ORDER BY A.moni_time
)

这个效率肯定不比以前的语句低。

另外inline view在内部机制上是不是创建了临是表？如果是的话，是否钳套了几层就有几个临时表？请指教。

jimmylee

最初由 jimmylee 发布
[B]另外对于我的问题，无法用中间表解决，因为我是做一个监控，数据时刻在变化，实时性比较强，但中间表对于相对变化不太频繁的数据是一个不错的选择。

[/B]

对不起，看到halaer的第一个总结的贴子就写了这些，看到第二个贴子才发觉上述说法欠妥，我将测试一下中间表，然后将结果告诉大家。

jimmylee

关于halaer的中间表的做法，我在100万条数据的表中做了测试，响应时间和我的语句的基本相同。（索引是moni_time列的索引).

drop table hits;
/

create table hits( sess_id number, seqno number, rid rowid, primary
key(sess_id,seqno) );
/


declare
  cnt integer;
begin
cnt := 0;
for y in ( select rrid FROM (SELECT ROWNUM rno, rid rrid
                    FROM (SELECT   ROWID rid
                              FROM cstmonilog2
                             WHERE moni_area = '9'
                               AND moni_typeid = '1'
                               AND moni_flag = '1'
                          ORDER BY moni_time) where rownum<=900035)
           WHERE rno >=900000)  
loop
cnt := cnt + 1;
insert into hits values ( userenv('sessionid'), cnt, y.rrid );
end loop;
end;
/

SELECT a.ROWID, a.moni_time, a.moni_type, a.moni_aperrcode, a.moni_constant,
SUBSTR (a.moni_userinfo, 1, 50) moni_userinfo
FROM cstmonilog2 a,(select * from hits where sess_id=userenv('sessionid')) b
where a.rowid=b.rid;
/

通常执行时，只执行后两部分，相应时间分别是7:22和5.47秒，总共为12.69秒。其中少了一个删除该session在hits表已有记录的动作。

我采用的语句
SELECT a.ROWID, a.moni_time, a.moni_type, a.moni_aperrcode, a.moni_constant,
SUBSTR (a.moni_userinfo, 1, 50) moni_userinfo
FROM cstmonilog2 a,
(SELECT rid
FROM (SELECT ROWNUM rno, rid
FROM (SELECT ROWID rid
FROM cstmonilog2
WHERE moni_area = '9'
AND moni_typeid = '1'
AND moni_flag = '1'
ORDER BY moni_time) where rownum<900035) --changed
WHERE rno >= 900000) b --changed
WHERE a.ROWID = b.rid
ORDER BY A.moni_time

执行时间是12.98秒

两者在执行效率上基本相同，因为在内部机制上也基本相同。不知道我的测试是否正确。

biti_rainy

hits  表不是临时插进去的
而是当你基表生成的时候自动维护的


可以用trigger
也可以用snapshot ( on  commit)

jimmylee

最初由 biti_rainy 发布
[B]hits  表不是临时插进去的
而是当你基表生成的时候自动维护的


可以用trigger
也可以用snapshot ( on  commit) [/B]

你的意思是当在cstmonilog中插入记录时维护hits表，是不是只需要两个字段(No number,rid rowid)，其中no是该记录在整个表中按moni_time排序后的位置，每次插入都要判断顺序，对多条记录做update动作，然后再插入新记录。这样理解应该不对吧，这样效率就太低了。能否谈谈如何在插入时维护。

biti_rainy

然后在时间字段上建立索引

事实上由于大部分记录基本应该是按照时间排列的，查询的时候会好的多

如果你的时间是按 sysdate  生成的而不是录入的，那最好：
利用序列，生成编号，然后在编号上建立索引，序列号就正好就是时间的顺序了