Limiting Result Sets

Most Database Management Systems these days come with built in functions like TOP, LIMIT or FIRST-n. What do they actually do? Theses functions pretty much limit the result set while querying from a table. These functions display first few records of a table when you don't need all the records. I have seen developers and database administrators executing a "Select" statement without limiting the resulting set when they just need one or two sample records. This adds overhead to the database and may cause performance issue when dealing with large tables. After reading this article, you will develop a reflex of using liming result sets when you do not need all the data.

Oracle Database 11g and prior releases are missing a limiting functions but there are few work around. May be Oracle 12c has a functions built in for limiting result, I strongly recommend you checking 12c features. Let's look at how you can limit the result set in Oracle 11g?  We are going to use ROWNUM to limit the query. ROWNUM is nothing but a pseudo-column that every Oracle Table has. To demo the limiting queries, we will use an employee table with 14 records.

Scenario 1: You are asked to display first 5 records from employee table.

SELECT column1, 
       column2 
FROM   (SELECT column1, 
               columnn2 
        FROM   employee) 
WHERE  ROWNUM <= 5;

The result will just display first 5 records from employee table. Another ways to get the similar result is using this query:

SELECT fname, 
       lname, 
       sal 
FROM   employee 
WHERE  ROWNUM <= 5; 

Scenario 2: Suppose, you are asked to display the name of  5 employees who make the most salary of all.Top-N analysis is used to display the most, least or random salary maker.

SELECT fname, 
       lname, 
       sal 
FROM   (SELECT fname, 
               lname, 
               sal 
        FROM   employee 
        ORDER  BY sal DESC)  --(Desc --High to Low)
WHERE  ROWNUM <= 5; 

Oracle 12c has introduced an exciting feature to limit the query result set therefore this article is for Oracle 11g and prior releases only. Below is the sample query syntax for 12c

select first, last, empno
     from employee
     FETCH FIRST 5 ROWS ONLY;

Oracle 12c limiting result set can do much more then I showed you here. I will soon have another article to cover the power of 12c query limiting the result sets.