The execution sequence of ROWNUM and ORDERBY in ORACLE Database

When SQL is used to query ORACLE table data, the following two results may be required. After sorting the query result set and getting the row number before sorting the result set, add the row number for each row to the above two results requirements. When writing an SQL statement, pay attention to the execution sequence of ROWNUM and ORDERBY. If the ORDERBY Benchmark

When SQL is used to query ORACLE table data, the following two results may be required. After sorting the query result set and getting the row number before sorting the result set, add the row number for each row to the above two results requirements. When writing an SQL statement, pay attention to the execution sequence of ROWNUM and order. If the ORDERBY Benchmark

Zookeeper

When SQL is used to query ORACLE table data, the following two results may be required.

1. Sorts the query result set and obtains the row number before sorting.

2. After sorting the result set, add a row number to each row

When writing SQL statements for the preceding two results, you must note the sequence of ROWNUM assignment and ORDER BY execution. If the benchmark field of ORDERBY is the PrimaryKey of the table, the query execution process is to sort the table first, and then assign the ROWNUM value to the sorted table view from the first row to the last row. Otherwise, if the base field of ORDERBY is not a PK, The ROWNUM value is assigned to the table from the first row to the last row, and then sorted. For example, if the table TABLE_TEST exists, the data is as follows.

TABLE_TEST

COLUMN_1 COLUMN_2 COLUMN_3 COLUMN_4 COLUMN_5

2011 Jim 010336633 Tokyo 19911011

2010 John 010336622 Beijing 19910609

2012 Kate 010336611 Newark 19920821

2013 Richard 010336644 Paris 19920115

2014 Joseph 010336666 London 19910726

The following SQL statement is used to query data.

Select rownum, COLUMN_1, COLUMN_2

FROM TABLE_TEST

Where rownum <3

Order by COLUMN_1 DESC;

Then, if

1. COLUMN_1 is the primary key of the table, and the query result is

   ROWNUM COLUMN_1 COLUMN_2

   1 2014 Joseph

   2 2013 Richard

2. If COLUMN_1 is not the table's primary key, the query result is

   ROWNUM COLUMN_1 COLUMN_2

   2 2011 Jim

   1 2010 John

From the above example, we can see whether the sorting column is the primary key, and the impact on the sorting and ROWNUM.

For the first two cases of the article, if we need to exclude the impact of the primary key of the sorting field on the execution order. You can use the following query methods.

1. SELECT *

   FROM (

   SELECT

   ROWNUM

   , COLUMN_1

   , COLUMN_2

FROMTABLE_NM

) Order by COLUMN_1

B. SELECTROWNUM, .*

FROM (

SELECT

COLUMN_1

, COLUMN_2

FROMTABLE_NM

ORDERBY COLUMN_1

)

Although the above method is not as efficient as non-nested queries, it can meet our query requirements.

Test version: ORACLE 11g Release 11.2

In addition, the ROW_NUMBER () OVER function added from ORACLE 9i is consistent with ROWNUM in the sorting relationship, but it has some more functions than ROWNUM, which can help implement the operations of the last several rows. For example

SELECT *

FROM (

SELECTA .*,

ROW_NUMBER () OVER (partition by trunc (COLUMN_1)

ORDERBY COLUMN_1 DESC) AS ROW_NUM

FROMTABLE_NM

) WHERE ROW_NUM <= 2