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...

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 v1as select count(1) count from t;View created.SQL> create view v2as select count(*) count from t;View created.SQL> select object_name, object_type, status from user_objectswhere (object_name in ('V1','V2'))and object_type = 'VIEW';OBJECT_NAME OBJECT_TYPE STATUS----------- ------------- --------V2 VIEW VALIDV1 VIEW VALIDAdd another column in the table.SQL> alter table t add ( c2 number);Table altered.SQL> select object_name,...

Read More