Dear expert,
in Oracle SQL, there is a possible criteria called rownum. Can i confirm that rownum will be executed at last as just a limit for number of records return? or could it be executed first, before other WHERE SQL criteria (let's if we put rownum prior to the others)?
It's not the equivalent of LIMIT in other languages. If you plan on limiting the number of records with rownum, you'll need to subquery the ORDER BY on the inside and use rownum in the outer query. Order of elements in your WHERE clause does not matter. See this excellent article by Tom Kyte.
Yes, within a WHERE clause, ROWNUM is always evaluated last, after all other predicates have been evaluated, regardless of their order.
It is evaluated before any GROUP BY, or ORDER BY clauses, however.