Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> selecting rowid slows down query

selecting rowid slows down query

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 25 Mar 2003 19:58:17 GMT
Message-ID: <b5qccp$2c9rou$1@ID-82536.news.dfncis.de>


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.html
Received on Tue Mar 25 2003 - 13:58:17 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US