CODEWITHSUNDEEP
sqlselectoracle11g
juniorbansal
juniorbansal

Reputation: 1271

Oracle Select query help please

SELECT  id
      FROM    ( 
 SELECT  id
      FROM   table      
      WHERE   
      PROCS_DT is null
      ORDER BY prty desc, cret_dt ) where rownum >0 and rownum <=100

The above query is giving me back 100 records as expected

 SELECT  id
          FROM    ( 
     SELECT  id
          FROM   table      
          WHERE   
          PROCS_DT is null
          ORDER BY prty desc, cret_dt ) where rownum >101 and rownum <=200

why is the above query returning me zero records?

Can some one help me how i can keep on. I am dumb in oracle...

Upvotes: 2

Views: 1399

Answers (2)

Dave Costa
Dave Costa

Reputation: 48121

ROWNUM values are assigned to rows as they are returned from a query (or subquery). If a row is not returned, it is not assigned a ROWNUM value at all; so the ROWNUM values returned always begin at 1 and increment by 1 for each row.

(Note that these values are assigned prior to any sorting indicated by the ORDER BY clause. This is why in your case you need to check rownum outside the subquery.)

The odd bit of logic you have to understand is that when you have a predicate on ROWNUM, you are filtering on a value that will only exist if the row passes the filter. Conceptually, Oracle applies any other filters in the query first, then tentatively assigns ROWNUM 1 to the first matching row and checks it against the filter on ROWNUM. If it passes this check, it will be returned with that ROWNUM value, and the next row will be tentatively assigned ROWNUM 2. But if it does not pass the check, the row is discarded, and the same ROWNUM value is tentatively assigned to the next row.

Therefore, if the filter on ROWNUM does not accept a value of 1, no rows will ever pass the filter.

The use of the analytic function ROW_NUMBER() shown in the other answers is one way around this. This function explicitly assigns row numbers (distinct from ROWNUM) based on a given ordering. However, this can change performance significantly, as the optimizer does not necessarily realize that it can avoid assigning numbers to ever possible row in order to complete the query.

The traditional ROWNUM-based way of doing what you want is:

SELECT  id
          FROM    (
  SELECT rownum rn, id
    FROM (
     SELECT  id
          FROM   table      
          WHERE   
          PROCS_DT is null
          ORDER BY prty desc, cret_dt
     ) where rownum <=200
  ) where rn > 101

The innermost query conceptually finds all matching rows and sorts them. The next layer assigns ROWNUMs to these and returns only the first 200 matches. (And actually, the Oracle optimizer understands the significance of a sort followed by a ROWNUM filter, and will usually do the sort in such a way as to identify the top 200 rows without caring about the specific ordering of the other rows.)

The middle layer also takes the ROWNUMs that it assigns and returns them as part of its result set with the alias "rn". This allows the outermost layer to filter on that value to establish the lower limit.

I would experiment with this variant and the analytic function to see which performs better in your case.

Upvotes: 3

Chandu
Chandu

Reputation: 82913

Try this:

SELECT id
FROM
  (SELECT id,
          rownum AS rn
   FROM
     (SELECT id
      FROM TABLE
      WHERE PROCS_DT IS NULL
      ORDER BY prty DESC, cret_dt) )
WHERE rn >101
  AND rn <=200

If you are comfortable using the ANALYTIC functions, try this:

SELECT id
  FROM 
    (
        SELECT id,
                ROW_NUMBER() OVER(ORDER BY prty DESC, cret_dt ) rn
            FROM table
        WHERE procs_dt IS NULL
    )
 WHERE rn >101 and rn <=200

Upvotes: 3

Related Questions