Get rows just I want

The Oracle latest version optimizers are working too smart. The comparison with oracle 9i and higher viersions.
I have a Query with an ORDER BY clause that returns 10,000 rows, but I'm restricting it to 500 rows with ROWNUM. Does the Oracle database store 10,000 rows in memory or open with 500 rows? For example:
SELECT empno, empnmFROM ( SELECT empno, empnm FROM employee ORDER BY empno )WHERE ROWNUM < 500;

Generally, the Oracle database does not store an entire result "in memory." Instead, the database tries to answer the query and return the first row before it gets the last row.
If the database attempted to store the result set in memory, we would never be able to ask a query like give more that one billion rows. Oracle generally answers the query on the fly whenever possible. In the above case—if EMPNO is indexed and EMPNO is NOT NULL—the optimizer will read the index to process the ORDER BY and stop after reading 500 rows.

Oracle 11g Result:

SQL_ORA11G> create table emp as select object_id empno, object_name ename from all_objects;
Table created.
SQL_ORA11G> select count(*) from emp;
COUNT(*)
----------
68503

SQL_ORA11G> create index emp_idx on emp(empno);
Index created.

SQL_ORA11G> analyze table emp compute statistics;
Table analyzed.

SQL_ORA11G> analyze index emp_idx compute statistics;
Index analyzed.

SQL_ORA11G> set autotrace traceonly
SQL_ORA11G> select empno, ename from ( select empno, ename from emp order by empno ) where rownum < 500;
Execution Plan
----------------------------------------------------------
Plan hash value: 2549860052
----------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------
0 SELECT STATEMENT 499 14970 6 (0) 00:00:01
* 1 COUNT STOPKEY
2 VIEW 499 14970 6 (0) 00:00:01
3 TABLE ACCESS BY INDEX ROWID EMP 68503 1873K 6 (0) 00:00:01
4 INDEX FULL SCAN EMP_IDX 499 3 (0) 00:00:01
---------------------------------------------------------
Oracle 9i Result:

The same script is working differently in the Oracle 9i. it is taking all the records and it is not stopping after it reaches 500. Oracle 9i reads all the data and filtering the 500 records.
SQL> create table emp as select object_id empno, object_name ename from all_objects;
Table created.

SQL> select count(*) from emp;
COUNT(*)
----------
29125

SQL> create index emp_idx on emp(empno);
Index created.

SQL> analyze table emp compute statistics;
Table analyzed.

SQL> analyze index emp_idx compute statistics;
Index analyzed.

SQL> set autotrace traceonly
SQL> select empno, ename from ( select empno, ename from emp order by empno ) where rownum < 500

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=194 Card=499 Bytes=873750) 1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=194 Card=29125 Bytes=873750)
3 2 SORT (ORDER BY STOPKEY) (Cost=194 Card=29125 Bytes=786375)
4 3 TABLE ACCESS (FULL) OF 'EMP' (Cost=15 Card=29125 Bytes=786375)

We try to retrieve the data from table with out order by. So it will not use any index and it goes for the FULL table scan.

Oracle 9i:
In Oracle 9i, it reads the 29125 records and giving the 499 records to outside.
SQL> select empno, ename from emp where rownum < 500;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=15 Card=499 Bytes=786375)
1 0 COUNT (STOPKEY) 2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=15 Card=29125 Bytes=786375)

Oracle 11g:
In Oracle 11g, it is going for the FULL table scan and it reads only 499 records but the actual count of the table is 68503. it is giving rows just I want.

SQL_ORA11G> select empno, ename from emp where rownum < 500;

Execution Plan
----------------------------------------------------
Plan hash value: 1973284518
----------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------
0 SELECT STATEMENT 499 13972 3 (0) 00:00:01
* 1 COUNT STOPKEY
2 TABLE ACCESS FULL EMP 499 13972 3 (0) 00:00:01
----------------------------------------------------

Read More

Count with View in Oracle 9i vs 11g

Oracle 9i Result:
SQL> drop table t;
Table dropped.

SQL> create table t (c number);
Table created.

SQL> create view v1
as select count(1) count from t;
View created.

SQL> create view v2
as select count(*) count from t;
View created.

SQL> select object_name, object_type, status from user_objects
where (object_name in ('V1','V2'))
and object_type = 'VIEW';

OBJECT_NAME OBJECT_TYPE STATUS
----------- ------------- --------
V2 VIEW VALID
V1 VIEW VALID

Add another column in the table.
SQL> alter table t add ( c2 number);
Table altered.

SQL> select object_name, object_type, status
2 from user_objects
3 where (object_name in ('V1','V2'))
4 and object_type = 'VIEW';

OBJECT_NAME OBJECT_TYPE STATUS
----------- ------------ -------------
V2 VIEW INVALID
V1 VIEW INVALID

Both the views are Invalid in Oracle 9i environment.
Oracle 11g Result:
SQL> drop table t;
Table dropped.

SQL> create table t (c number);
Table created.

SQL> create view v1
2 as
3 select count(1) count from t;
View created.

SQL> create view v2
2 as
3 select count(*) count from t;
View created.

SQL> select object_name,object_type,status
2 from user_objects
3 where ( object_name in ( 'V1','V2'))
4 and object_type ='VIEW';

OBJECT_NAME OBJECT_TYPE STATUS
----------- -------------- -------------
V1 VIEW VALID
V2 VIEW VALID

Add another column in the table.

SQL> alter table t add(c2 number);
Table altered.

SQL> select object_name,object_type,status
2 from user_objects
3 where ( object_name in ( 'V1','V2'))
4 and object_type ='VIEW';

OBJECT_NAME OBJECT_TYPE STATUS
------------ --------------- -------------
V1 VIEW INVALID
V2 VIEW VALID


The V1 became Invalid. but V2 is valid

Read More