Rule Definition
Using ROWNUM in a query can affect view optimization.
The ROW_NUMBER built-in SQL function provides superior support for ordering the results of a query.
The row_limiting_clause of the SELECT statement provides superior support for limiting the number of rows returned by a query.
In Oracle < 10.2.0.4 ROWNUM=1 lead to performance issue, ROWNUM<2 is to be used instead (Bug 4513695 Poor performance for SELECT with ROWNUM=1 with literal replacement, fixed in 10.2.0.4.)
Remediation
Change the query using ROW_NUMBER built-in SQL function or using row_limiting_clause of the SELECT statement
Reference
http://docs.oracle.com/database/121/SQLRF/pseudocolumns009.htm#SQLRF00255
Bug 4513695: https://support.oracle.com/rs?type=bug&id=4513695
Related Technologies
Technical Criterion
Programming Practices - Structuredness
About CAST Appmarq
CAST Appmarq is by far the biggest repository of data about real IT systems. It's built on thousands of analyzed applications, made of 35 different technologies, by over 300 business organizations across major verticals. It provides IT Leaders with factual key analytics to let them know if their applications are on track.