Oracle-L: ROWNUM use generating different execution plans

From: Amar Kumar Padhi <TS2017_at_emirates.com>
Date: Mon, 24 Jun 2002 23:08:20 -0800
Message-ID: <F001.004863FC.20020624230820@fatcity.com>

"rownum = 1" and "rownum < 2" should behave in the same way, i.e., it should generate a COUNT (STOPKEY) execution. Strangely, I have the following situation where a table is giving different execution plans. When I create the same table in different database, it gives me the same execution plan for both options. What may possibly be wrong in the present one?

Oracle 8.1.7.1/sql*plus 8.0.6/Optimizer=rule

SQL>select * from am33;

COL1

3
4
5

SQL>select * from am33 where col1 = 4 and rownum = 1;

COL1

Execution Plan

0 SELECT STATEMENT Optimizer=RULE 1 0 COUNT

   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'AM33'

SQL>select * from am33 where col1 = 4 and rownum < 2;

COL1

Execution Plan

0 SELECT STATEMENT Optimizer=RULE 1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF 'AM33' rgds
amar
http://amzone.netfirms.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Amar Kumar Padhi
  INET: TS2017_at_emirates.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Jun 25 2002 - 02:08:20 CDT