RE: Table Scan Question

From: yong huang <yong321_at_yahoo.com>
Date: Fri, 5 Jan 2001 09:56:48 -0800 (PST)
Message-Id: <10732.125853@fatcity.com>

Hi, Antonio,

It's not correct to say that you get a full table scan if you have ROWNUM < [some number] in the WHERE clause. The explain plan is misleading. Oracle plans ahead to do a full table scan but it stops after a certain number of rows are scanned. That's what STOPKEY means. If you have doubt, do a simply test like this:
set timing on
select * from a_big_table where rownum = 1; select * from a_big_table;
The order of running them matters for reason of data caching.

Yong Huang
yong321_at_yahoo.com

you wrote:

You can use "where rownum < 1000" but it will do a full table scan anyway:

HS_DSV-PHS10-Linux > delete from teste
  2 where rownum < 1001;

1000 rows deleted.

Execution Plan

---

   0 DELETE STATEMENT Optimizer=CHOOSE    1 0 DELETE OF 'TESTE'

   2    1     COUNT (STOPKEY)
   3    2       TABLE ACCESS (FULL) OF 'TESTE'

__________________________________________________

Do You Yahoo!?
Yahoo! Photos - Share your holiday photos online! Received on Fri Jan 05 2001 - 11:56:48 CST