Methods of RowNum and Row_number () use in Oracle

Oracle in RowNum

The number of the row returned from the query, the first row returned is 1, the second row is 2, and so on, this pseudo field can be used to limit the total number of rows returned by the query, and rownum cannot be prefixed with the name of any table.

1, rownum for equal to a value of the query conditions
If you want to find information about the first student in the student table, you can use Rownum=1 as a condition. But to find the second student in the student table, the data is not available using the rownum=2 results. Since rownum are all starting from 1, but more than 1 of the natural numbers in rownum do equal to the judgment is considered false condition, so can not find rownum = N (n>1 natural number).
Sql> Select Rownum,id,name from student where rownum=1; --There are records
Sql> Select Rownum,id,name from student where rownum = 2; --No record

2, rownum for more than a certain value of the query conditions
If you want to find a record from the second row, and you can't find the record using ROWNUM>2, you can use the following subquery method to resolve it. Note that the rownum in the subquery must have an alias, or the record will not be detected, because rownum is not a column of a table, and it is not possible to know whether RowNum is a column of a subquery or a column of the main query without an alias.
Sql>select * FROM (select RowNum No, id,name from student) where no>2; --There are records
Sql> SELECT * FROM (select Rownum,id,name from student) where rownum>2; --No record

3, rownum for less than a value of the query conditions
If you want to find the previous record of the third record, you can get two records when using Rownum<3. Obviously rownum for rownum<n (the natural number of n>1) is considered to be tenable, so you can find the record.
Sql> Select Rownum,id,name from student where RowNum <3; --There are records

4, RowNum and sorting
The rownum in Oracle is the sequence number that is produced when the data is fetched, so it is important to be aware of the rowmun rows of data that you want to specify for the sorted data.
Sql> Select RowNum, id,name from student order by name;

RowNum ID NAME
---------- ------ ---------------------------------------------------
3 200003 Lie triple systems
2 200002 King Two
1 200001 Sheets A
4 200004 Zhao Si

As you can see, rownum is not the serial number generated by the Name column. The system is the number of records in the order in which they were inserted, and ROWID is also assigned sequentially. To solve this problem, you must use a subquery

Sql> Select RowNum, Id,name from (SELECT * to student order by name);

RowNum ID NAME
---------- ------ ---------------------------------------------------
1 200003 Lie triple systems
2 200002 King Two
3 200001 Sheets A
4 200004 Zhao Si

Second, Oracle in Row_number ()

1, Row_number () over (order by col_1[,col_2 ...])
According to Col_1[,col_2 ...] Sort, returns the sorted result set, and returns a different value for each row.

2, Row_number () over (partition by col_n[,col_m ...] order by col_1[,col_2 ...])
Follow the Col_n[,col_m first. To group, and then in each group according to Col_1[,col_2 ...] To sort (ascending), and finally to return a sorted result set

Oracle Row_number () instance
1. Use the Row_number () function for numbering, as
Select Email,customerid, Row_number () over (order by PSD) as rows from Qt_customer
Principle: First, according to the PSD to sort, after sorting, the number of each piece of data.

2. Sort the order by the ascending price, and sort the code for each record as follows:
Select Did,customerid,totalprice,row_number () over (totalprice) as rows from Op_order

3, statistics of each customer's recent orders is the first few orders.
With tabs as
(
Select Row_number () over (partition by CustomerID ORDER by Totalprice) as Rows,customerid,totalprice, DID from Op_order
)
Select MAX (rows) as ' next single times ', CustomerID from tabs group by CustomerID

4, in the use of over and so open window functions, over inside the grouping and sorting execution later than "Where,group By,order by" implementation
。
Select
Row_number () over (partition by CustomerID ORDER by INSDT) as rows,
Customerid,totalprice, DID
From Op_order where insdt> ' 2011-07-22 '
The above code executes the WHERE clause first, and then the number of each record is executed.


iii. The difference between row_number () and RowNum

When you use RowNum to sort, you first add a fake rownum to the result set before you sort it, and row_number () sorts and then calculates the line number after the sort clause is included.

Attached question: There is one SQL statement:

SELECT *
From (
SELECT t.*, Row_number () over (order by ID) RN
From MyTable t
)
WHERE RN Between:start And:end
The ORDER BY statement in SQL greatly reduces the processing speed, and if the order is removed, the corresponding execution plan is greatly improved. If you change to the following SQL:

SELECT t.*, Row_number () over (order by ID) RN
From MyTable t
WHERE rownum Between:start And:end
Obviously, this SQL is wrong and cannot query the correct data information at all. Is there any other way to improve the query speed?
For the above questions, you have to understand the difference between row_number and rownum, and how to use this information.

First, take a look at how rownum works, according to Oracle's official documentation:
This comparison returns false directly if the rownum is greater than the comparison. For example, the following SQL statement will not return any data information:

SELECT *
From Employees
WHERE rownum > 1
In a query, the first hit data is given a pseudo column rownum of 1, and the condition is false. The second hit data, because the first false will be the first data, still assigns a value of 1, indicating that the condition is still false. All subsequent data will repeat this logic, and the last piece of data is not returned.

That's why the previous 2nd query should be converted to the following SQL statement:

SELECT *
From (
SELECT t.*, rownum as RN
From MyTable t
ORDER BY
Paginator, ID
)
WHERE RN Between:start And:end
Next, you need to create some temporary data tables to view the execution performance of this SQL statement, we will create a temporary table, append the index, then populate the data, and finally analyze the query information for this SQL statement.

CREATE TABLE MyTable (
ID Number (TEN) is not NULL,
Paginator number is not NULL,
Value VARCHAR2 (50)
)
/
ALTER TABLE MyTable
ADD CONSTRAINT pk_mytable_id PRIMARY KEY (ID)
/

CREATE INDEX ix_mytable_paginator_id on MyTable (paginator, id)
/

INSERT
into MyTable (ID, paginator, value)
SELECT level, level/10000, ' Value ' | | Level
From dual
CONNECT by
Level <= 1000000
/

COMMIT
/

BEGIN
Dbms_stats.gather_schema_stats (' "20090506_rownum");
End;
/
This SQL statement creates a table that includes 1 million data and creates a federated index.
At the same time, in this query, the Patinator field is not unique, in order to later show such a phenomenon:
In a query, some data may appear multiple times in a different paging query, and some data may be based on a query that is not queried
This is called paging chaos.

Then, separate queries using Row_numer and rownum, respectively, to return 10 data information from 900001 to 900010.
Row_number ()

SELECT *
From (
SELECT t.*, Row_number () over (order by paginator, id) as RN
From MyTable t
)
WHERE RN BETWEEN 900001 and 900010
ID paginator VALUE RN
900001 Value 900001 900001
900002 Value 900002 900002
900003 Value 900003 900003
900004 Value 900004 900004
900005 Value 900005 900005
900006 Value 900006 900006
900007 Value 900007 900007
900008 Value 900008 900008
900009 Value 900009 900009
900010 Value 900010 900010
Ten rows fetched in 0.0005s (0.8594s)
SELECT STATEMENT
VIEW
WINDOW Nosort Stopkey
TABLE ACCESS by INDEX ROWID, 20090506_rownum. MYTABLE
INDEX full SCAN, 20090506_rownum. ix_mytable_paginator_id
RowNum

SELECT *
From (
SELECT t.*, rownum as RN
From (
SELECT *
From MyTable
ORDER BY
Paginator, ID
) T
)
WHERE RN BETWEEN 900001 and 900010
ID paginator VALUE RN
900001 Value 900001 900001
900002 Value 900002 900002
900003 Value 900003 900003
900004 Value 900004 900004
900005 Value 900005 900005
900006 Value 900006 900006
900007 Value 900007 900007
900008 Value 900008 900008
900009 Value 900009 900009
900010 Value 900010 900010
Ten rows fetched in 0.0005s (0.7058)
SELECT STATEMENT
VIEW
COUNT
VIEW
TABLE ACCESS by INDEX ROWID, 20090506_rownum. MYTABLE
INDEX full SCAN, 20090506_rownum. ix_mytable_paginator_id
As you can see from the above, queries using RowNum are slightly faster than the Row_number function.
Then looking at a row_number query, you can see that Oracle is smart enough to avoid sorting by using federated indexes, and then you can quickly find the appropriate data information directly by using the Stopkey operation.
The rownum query also uses the index, but does not take advantage of the stopkey condition, just a simple count operation.
So, can also let rownum use Stopkey. In the previous query, Oracle did not know that this RN was an internal query for rownum aliases, we can rewrite the query, use rownum in the outer query, so that we can use the Stopkey condition in the outer layer. This is the variant of our common Oracle3-layer paging:

SELECT *
From (
SELECT t.*, rownum as RN
From (
SELECT *
From MyTable
ORDER BY
Paginator, ID
) T
)
WHERE RN >= 900001
and RowNum <= 10
ID paginator VALUE RN
900001 Value 900001 900001
900002 Value 900002 900002
900003 Value 900003 900003
900004 Value 900004 900004
900005 Value 900005 900005
900006 Value 900006 900006
900007 Value 900007 900007
900008 Value 900008 900008
900009 Value 900009 900009
900010 Value 900010 900010
Ten rows fetched in 0.0005s (0.4714s)
SELECT STATEMENT
COUNT Stopkey
VIEW
COUNT
VIEW
TABLE ACCESS by INDEX ROWID, 20090506_rownum. MYTABLE
INDEX full SCAN, 20090506_rownum. ix_mytable_paginator_id
In this query, Oracle took advantage of the Stopkey, with a speed of only 471ms, faster than the original.

If Row_number and rownum use the same execution plan, why is rownum significantly faster?
This is because Oracle's history is too long, and different times lead to the same characteristics but have different effects.

RowNum was introduced in Oracle6, published in 1988, at that time what resources and conditions are not satisfied, as a simple counter, is considered very simple and efficient.
And with the development of the times, more demand is mentioned, at this time, a function equivalent but more powerful than rownum is introduced, this is the Row_number function, which is introduced from Oracle9i. At this time, efficiency is no longer the only condition, so the implementation of Row_number is no longer the only indicator of efficiency.

Of course, if you have more requirements, such as grouping sorting and so on, you need to use the Row_number function, but if you are just a simple paging query, suggest using RowNum, which is why in the current era rownum is still so popular ( It is said that there is an offset paging operator in the oracle12c, and the Row_number function is used internally so that RowNum can retire.