---------------------------------------------------------------------------------------------------- Most developers don't know the benefit brought by rownum pseudocolumn in the case below: select count(*) into cnt from bigtable; if cnt > 0 then do_something; end if; can be changed to select count(*) into cnt from bigtable where rownum = 1; if cnt = 1 then --cnt > 0 works as well do_something; end if; as long as do_something does not actually use the value of cnt. And of course they don't know why the -l option for the UNIX command grep can significantly speed up file pattern match, when all they need is to know whether the pattern exists in the file. ---------------------------------------------------------------------------------------------------- Rownum helps avoid reading chained rows. http://www.itpub.net/thread-1278548-1-1.html message #8 on A simple case to test our theory is: create table testlong (x int, y long); --my db_block_size is 4, so 5000 bytes is enough to cause a row to chain insert into testlong values (1, rpad('x', 5000)); insert into testlong values (2, rpad('x', 5000)); insert into testlong values (3, rpad('x', 5000)); commit; --confirm chained_rows analyze table testlong list chained rows; select * from chained_rows; --In my 10gR2, 'table fetch continued row' statistic# is 252 --Get base value select value from v$mystat where statistic# = 252; select * from testlong where rownum < 2; --Should increase by 1 select value from v$mystat where statistic# = 252; --Run the two above queries a few times --Add rownum stop key select count(*) from select * from testlong where rownum < 2); --stat value should not increase because of this rownum optimization; you can change 2 to 3 and it won't change either select value from v$mystat where statistic# = 252; ---------------------------------------------------------------------------------------------------- Jonathan Lewis "Cost-Based Oracle Fundamentals", pp.398-399