"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
__________
4
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
__________
4
Execution Plan
__________________________________________________________
0 SELECT STATEMENT Optimizer=RULE
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF 'AM33'
rgds
amar
http://amzone.netfirms.com
