how can I fetch even or odd records from a table?

I have tried to display odd or even records using below two queries.

select * from employee a 
    where 1 = mod(rownum,2);
select * from employee 
   where (rowid,1) in (select rowid, mod(rownum,2) 
                       from employee);

First one displays only the first record of the employee table. whereas second query works perfectly. Please, can any one explain how rownum works??.

Solution

This is because of a peculiarity of rownum. It's a pseudo-column which is applied to the result set returned by the query. (This is why WHERE ROWNUM > 1 is always false.)

In this case the use of ROWNUM causes the query to stop when the WHERE clause returns false. So this returns no rows (because 0 is only returned for even-numbered rows):

select * from employee a 
    where 0 = mod(rownum,2);

Your second approach has a subquery which doesn't use ROWNUM in a WHERE clause, and so allows the whole table to be returned for evaluation in the outer query.

Any approach which allows the materialization of the entire result set without evaluating ROWNUM will work. This will also produce the result you want:

select * from
    (select a.*, rownum as rn from employee a)
where mod(rn,2) = 1
/

As @DavidAldridge points out in his comment, without an ORDER BY clause the result set is essentially random. ROWNUM doesn't play nice with ORDER BY, so to guarantee the ordering use the analytic function ROW_NUMBER() instead.

select * from
    (select a.*
            , row_number() over (order by a.emp_id)  as rn 
     from employee a)
where mod(rn,2) = 0
/

" how bellow query fetches only first two records from table."

Through the wonders of the COUNT STOPKEY operation. The query knows how many rows are expected; it returns rows (and assigns values of ROWNUM) until that limit is reached.

We can see this in the EXPLAIN PLAN. Without the filter:

SQL> explain plan for 
  2      select * from emp;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |   111 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |     3 |   111 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

12 rows selected.

SQL>

Here is the plan with where rownum <= 2 . Note the difference in rows selected:

SQL> explain plan for 
  2      select * from emp
  3      where rownum <= 2;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1973284518

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    74 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |     3 |   111 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=2)

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.

SQL>