Suppose we want to retrieve and see only few records with specific condition. We do not want to see all such records. When we have very large tables, querying and displaying all the records will be time consuming. We just want to see few records with specific criteria to understand the records or for some analysis. Hence we need to restrict the number of records queried. In order to do this, we use ROWNUM operator which will have count of the records.
Suppose we want to see first ten records of employees who work for DEPT_ID = 10.
SELECT * FROM EMPLOYEE WHERE DEPT_ID =10 AND ROWNUM <= 10;