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

oracle问题集

[e-mail]> /

OWNER OBJECT_NAME OBJECT_ID
------------------------------ ------------------------------ ----------
SYS CLU$ 5
SYS CLU$ 5
SYS CLU$ 5
SYS CLU$ 5
SYS CLU$ 5
SYS I_TS# 7
SYS I_TS# 7
SYS I_TS# 7
SYS I_TS# 7
SYS I_TS# 7
SYS I_TS# 7

11 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=53053 Card=30 Bytes=1800)
1 0 VIEW (Cost=53053 Card=30 Bytes=1800)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=53053 Card=52483 Bytes=2466701)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=53053 Card=52483 Bytes=4513538)
5 4 INDEX (FULL SCAN) OF 'T_OID_IND' (NON-UNIQUE) (Cost=101 Card=52952)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
33 consistent gets ***********************
0 physical reads
0 redo size
587 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed


Please notice the line I marked with '**********'. Now let's create index on 
(object_name,object_id), and query in your way:

[e-mail]> analyze table t delete statistics;

Table analyzed.
[e-mail]>l
1* create index t_oname_oid_ind on t (object_name,object_id) nologging pctfree 0
[e-mail]>/

Index created.

[e-mail]>l
1 select owner,object_name,object_id
2 from t a,
3 ( select rid from
4 ( select rownum rno,rowid rid from
5 ( select rowid from t
6 where object_name < 'Z'
7 order by object_id
8 )
9 ) where rNo between 20 and 30
10 ) b
11 where a.rowid=b.rid
12* order by a.object_id
[e-mail]> /

OWNER OBJECT_NAME OBJECT_ID
------------------------------ ------------------------------ ----------
SYS CLU$ 5
SYS CLU$ 5
SYS CLU$ 5
SYS CLU$ 5
SYS CLU$ 5
SYS I_TS# 7
SYS I_TS# 7
SYS I_TS# 7
SYS I_TS# 7
SYS I_TS# 7
SYS I_TS# 7

11 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 NESTED LOOPS
3 2 VIEW
4 3 COUNT **************
5 4 VIEW
6 5 SORT (ORDER BY)
7 6 INDEX (RANGE SCAN) OF 'T_ONAME_OID_IND' (NON-UNIQUE)
8 2 TABLE ACCESS (BY USER ROWID) OF 'T'

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
220 consistent gets *****************
208 physical reads *****************
0 redo size
587 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
11 rows processed

So it's quite clear the count (stopkey) (Stopkey is a new method introduced in 
8i and later version. it's implemented mostly for getting TOP N records. )is 
more efficient in this cirmstance. But as you browse page by page, it will 
become less efficient. Any way it won't be slower than count operation. As Bitty 
said, few people will browse the whole resultset page by page. If most of 
browsing are focusing at the 1st 500 rows, using count(stopkey), if more of 
browsing are focusing at the last 500rows, still suggest to use count(stopkey), 
but index ur key columns in desc order. But if the user's action is ramdom, 
Jimmy's may serve the purpose. However, the people using this one must consider 
the CBO's negative affection:

[e-mail]> set autot traceo
[e-mail]> analyze table t compute statistics for table for all 
indexes for all indexed columns;

Table analyzed.

[e-mail]>
[e-mail]>
[e-mail]> 1 select owner,object_name,object_id
2 from t a,
3 ( select rid from
4 ( select rownum rno,rowid rid from
5 ( select rowid from t
6 where object_name < 'Z'
7 order by object_id
8 )
9 ) where rNo between 20 and 30
10 ) b
11 where a.rowid=b.rid
12* order by a.object_id
/

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=959 Card=52483 Bytes=3306429)
1 0 SORT (ORDER BY) (Cost=959 Card=52483 Bytes=3306429)
2 1 HASH JOIN (Cost=416 Card=52483 Bytes=3306429)
3 2 VIEW (Cost=287 Card=52483 Bytes=1049660)
4 3 COUNT
5 4 VIEW (Cost=287 Card=52483 Bytes=367381)
6 5 SORT (ORDER BY) (Cost=287 Card=52483 Bytes=1364558)
7 6 INDEX (FAST FULL SCAN) OF 'T_ONAME_OID_IND' (NON-UNIQUE) (Cost=22 Card=52483 
bytes=1364558)
8 2 TABLE ACCESS (FULL) OF 'T' (Cost=58 Card=52952 Bytes=2276936)

Statistics
----------------------------------------------------------
42 recursive calls
0 db block gets
805 consistent gets ****************
0 physical reads
0 redo size
587 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
11 rows processed

In fact, the total blocks of table t is only 583 blocks. And one more, this 
method will also lose efficiency its competitiveness when the avg_row_len is 
small. So, please remember to include the /*+rule*/ hint in your statement if 
you want to use this method. 
__________________
If not me, who? If not now, when?
------------------
MSN:[e-mail]


由 hahaer 于 02-08-03 09:18 发表: 
Now let's moves Bitty's method, mid table(or hit table). The following is from 
asktom.oracle.com, it's very clear and understandable.


  quote:


  o Lets say the query is like the above however it takes a 'long' time to 
  execute. I would set up a 'hits' table like the following:

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


  Then, when I wanted to search I would code something like:

  cnt := 0;
  for y in ( select t.rowid rid from t where x = :host_variable order by y ) 
  loop
  cnt := cnt + 1;
  insert into hits values ( userenv('sessionid'), cnt, y.rid );
  end loop;

  You have to pass that userenv('sessionid') from page to page as a hidden 
  variable then to identify the result set.

  And to display results from this i would query:


  select * from t, hits
  where hits.seqno between :lowBound and :highBound
  and hits.sess_id = :theSessionId
  order by hits.sess_id, hits.seqno

  Normally I keep another 'parent' table to go along with hits and I store the 
  session id, the TIME of the query and such in there as well. I put a foreign 
  key from hits back to this parent with ON DELETE CASCADE and run a job 
  (scheduled via dbms_jobs) to issue "delete from parent_table where time < 
  sysdate-1/24" -- that cleans out queries older then 1 hour. Occasionally, i 
  truncate the table to lower the high water mark as well.







At fast glance, it seems like similar with the Jimmy's. In fact, they are 
different. the hit table is created one time, while use many, howevery, Jimmy's 
will ffs index everytime when paging. It also eliminates the small avg_row_len's 
affection at most.
__________________
If not me, who? If not now, when?
------------------
MSN:[e-mail]


由 jimmylee 于 02-08-04 22:51 发表: 
受益非浅，颇有tom kyte风范！如果提问或回答问题都象这样一步步有条理，问题才能讲清楚。佩服，佩服，值得学习！


由 jimmylee 于 02-08-05 00:11 发表: 
非常感谢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 于 02-08-05 00:19 发表: 
  quote:

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






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


由 jimmylee 于 02-08-05 02:28 发表: 
关于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 于 02-08-05 03:07 发表: 
no no
hits 表不是临时插进去的
而是当你基表生成的时候自动维护的


可以用trigger
也可以用snapshot ( on commit)
__________________
I love oracle
But i hate IT

oracle文档
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage?remark=tahiti


由 jimmylee 于 02-08-05 03:21 发表: 
Re: no no
  quote:

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


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




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


由 biti_rainy 于 02-08-05 16:41 发表: 
直接把时间字段放在hits表中
然后在时间字段上建立索引

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

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

oracle文档
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage?remark=tahiti



所有时间均为 +8 hours . 现在时间是 22:06.
Powered by: vBulletin Version 2.0.1
Translated and hacked by:Ztbbs.
Copyright &copy; Jelsoft Enterprises Limited 2000, 2001.
Copyright by itpub.net