Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> selecting rowid slows down query
Hello
Oracle 9i, Windows and Solaris.
If I do a repeated full table scan and query that table's rowid, each query is slower than the preceding:
create table slow_rowid as
select
a.object_name || b.object_name foo, rownum bar
from
all_objects a cross join all_objects b
where
rownum <= 100000;
create procedure p_slow_rowid as
begin
for r in (select rowid, foo, bar from slow_rowid) loop
null;
end loop;
end;
/
set timing on
exec p_slow_rowid exec p_slow_rowid exec p_slow_rowid exec p_slow_rowid exec p_slow_rowid exec p_slow_rowid exec p_slow_rowid exec p_slow_rowid exec p_slow_rowid exec p_slow_rowid exec p_slow_rowid
I don't have an idea for this observation. Can someone explain why that happens and if it is possible to circumvent this behaviour?
Thanks
Rene
-- Projektleitung und Entwicklung in Oracle/C++/C# Projekten http://www.adp-gmbh.ch/cv.htmlReceived on Tue Mar 25 2003 - 13:58:17 CST