RowNum and order by execution in Oracle database

When you use SQL to query Oracle table data, there are two possible results requirements.

Sort the query result set and get the line number before it is sorted

After you sort the result set, add line numbers for each row

For both of these result requirements, you need to be aware of the order in which rownum assignments and orders by are executed when writing SQL statements. If the base field for order by is the PrimaryKey of a table, the query execution process is to sort the table, and then assign rownum values from the first row to the last row for the sorted table view. Conversely, if the base field for order by is not PK, then the rownum value is assigned from the first row to the last behavior table, and then sorted.

For example, suppose there is a table table_test, whose data is as follows.

Table_test

column_1 column_2 column_3 column_4 column_5

Jim 010336633 Tokyo 19911011

John 010336622 Beijing 19910609

Kate 010336611 Newark 19920821

2013 Richard 010336644 Paris 19920115

2014 Joseph 010336666 London 19910726

The following SQL statements are queried for data.

SELECT rownum, column_1, column_2

From Table_test

WHERE RowNum < 3

Order by column_1 DESC;

Well, if

Column_1 is the primary key of the table, the query result is

RowNum column_1 column_2

1 2014 Joseph

2 2013 Richard

Column_1 is not a primary key of a table, the query result is

RowNum column_1 column_2

2 Jim

1 John

From the above example, we can see the effect of sorting and rownum based on whether the row sequence is the primary key.

for the first two cases of the article, if we need to eliminate the effect of the sort field primary key on the order of execution. You can use the following query method separately.

SELECT *

From (SELECT rownum, column_1, column_2

From TABLE_NM

) Order BY column_1

B. SELECT RowNum, A.*

From (SELECT column_1, column_2

From TABLE_NM

ORDER BY column_1

) A

Using the above approach, although not as efficient as not nested query, but to meet our query requirements.

Beta version: ORACLE 11g release 11.2

In addition, the row_number () over function, which was added from Oracle 9i, is consistent with rownum in the sort relationship, but it has more functionality than rownum, which can help implement the last few rows.

For example

SELECT *

From (

Selecta.*,

Row_number () over (PARTITION by TRUNC (column_1)

by Column_1 DESC) as Row_num

FROMTABLE_NM A

WHERE row_num <=2