Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    15

    Unanswered: ROWNUM and ORDER BY

    Just wanted to know in which order the db will execute my query if my query contains a 'WHERE ROWNUM < 1000' and an 'ORDER BY ...'.
    The documentation says that the order of evauation depends upon the
    indexes used in the ORDER BY, but doesn't specify clearly in which order.

    SELECT ... FROM ...
    WHERE ...
    and ROWNUM < 1000
    ORDER BY ....

    Will the db fetch the first 1000 and sort that or will it sort the entire set and then fetch the first 1000 of that sorted set ?

    Please help.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: ROWNUM and ORDER BY

    The ROWNUM is applied before the ORDER BY. To get the other way round do this:
    PHP Code:
    SELECT ... FROM
    SELECT ... FROM ...
      
    WHERE ...
      
    ORDER BY ....
    )
    WHERE ROWNUM 1000

  3. #3
    Join Date
    Mar 2004
    Location
    India
    Posts
    15

    Re: ROWNUM and ORDER BY

    Thanks andrewst for your reply.

    This is what the documentation says -

    ----------------------------------------------------------------

    You can use ROWNUM to limit the number of rows returned by a query, as in this example:

    SELECT * FROM employees WHERE ROWNUM < 10;

    If an ORDER BY clause follows ROWNUM in the same query, then the rows will be reordered by the ORDER BY clause.
    The results can vary depending on the way the rows are accessed.

    For example, if the ORDER BY clause causes Oracle to use an index to access the data, then Oracle may retrieve the rows in a different order than without the index.

    Therefore, the following statement will not have the same effect as the preceding example:

    SELECT * FROM employees WHERE ROWNUM < 11 ORDER BY last_name;

    If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows.

    For example, the following query returns the 10 smallest employee numbers. This is sometimes referred to as a "top-N query":

    SELECT * FROM
    (SELECT * FROM employees ORDER BY employee_id)
    WHERE ROWNUM < 11;

    In the preceding example, the ROWNUM values are those of the top-level SELECT statement, so they are generated after the rows have already been ordered byemployee_id in the subquery.

    ----------------------------------------------------------------

    Am confused !
    Last edited by sjanarth; 03-16-04 at 11:37.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: ROWNUM and ORDER BY

    What are you confused by? I am guessing it is this part:

    The results can vary depending on the way the rows are accessed.
    You can think of the process as being something like this:
    1) Select the data
    2) Apply the ROWNUM
    3) Apply the ORDER BY

    Now, if Oracle uses an index to access the data, then the data will be selected in the order of the index. Let's do an example:
    PHP Code:
    SQLcreate table t (a intb int);

    Table created.

    SQLinsert into t values (1,7);

    1 row created.

    SQLinsert into t values (2,4);

    1 row created.

    SQLinsert into t values (3,8);

    1 row created.

    SQLinsert into t values (4,1);

    1 row created.

    SQLselect from t;

             
    A          B
    ---------- ----------
             
    1          7
             2          4
             3          8
             4          1

    SQL
    select from t where b and rownum 1;

             
    A          B
    ---------- ----------
             
    1          7

    SQL
    create index i on t(b);

    Index created.

    SQLselect from t where b 0;

             
    A          B
    ---------- ----------
             
    4          1
             2          4
             1          7
             3          8

    SQL
    select from t where b and rownum 1;

             
    A          B
    ---------- ----------
             
    4          1 
    When the index on b exists, Oracle uses it to select the data, so it gets the data ordered by b. So when ROWNUM is applied, we get a different subset of the rows than without the index.

  5. #5
    Join Date
    Aug 2010
    Location
    Bangalore
    Posts
    7
    Hi

    1. The FROM/WHERE clause goes first.
    2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
    3. SELECT is applied.
    4. GROUP BY is applied.
    5. HAVING is applied.
    6. ORDER BY is applied.


    So for query like below

    select id from TableX where rownum < 10 order by id

    The oracle will select 10 rows and then applies the order by for that 10 rows.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •