The difference between rownum, ROWID, Row_number (), rank (), Dense_rank () in Oracle __oracle

First of all, rownum and rowid meaning:

As the name implies RowNum is the line number/line number, and ROWID is the code/number/Unique identification number, so he is similar to the "Aaar8gaaeaaaaeraak" number, notice that he is not in order, that is, he and data warehousing time has no relationship, for example: he is like a disk, The memory store data is in the same address as the 16 feed.

They are all pseudo columns and can be understood as a column in a table just because they are not what you created. What is the same pseudo-column difference?

ROWID is when you enter data. The only 18-digit number that is automatically added to this record is a physical number used to find this record (which, incidentally, is why the emphasis on using ROWID is emphasized when data is tuned), he's not going to change with the query unless the table is moved ( such as table space changes, data import/export after the changes will occur.

The rownum is automatically added based on the results of the SQL query, but he is not affected by the order by sort in SQL, because he is the same as the ROWID in that the system gives the record number (sequential, no hops) in the order in which the records are inserted. But if you want rownum to be in the same order as ordering by, then you can use a subquery, like this: select Rownum,t.* from (SELECT * from table space name to field name) T so rownum is sorted according to the field The number, why it is so, I understand: RowNum is based on the table record output line number, and filter statements, sort statements are not related to the query when the subquery is equal to generate a table and then according to this table starting from 1 sorted. Similarly, you can use the row_number () over in the analysis function that you want to extract (the name of the field that the order by needs to be sorted).

It is worth mentioning that the MSSQL is not rownum and ROWID.

The Analytic function row_number (), rank (), Dense_rank ().

Row_number ():
The Row_number function returns a unique value, and when the same data is encountered, the rankings are incremented in the order in which they are recorded in the recordset. Row_number () and rownum similar, more powerful (can be sorted from 1 in each group), because the Row_number () is the analysis function and rownum is pseudo column so row_number () must over and rownum can not over.

RANK ():
The Rank function returns a unique value, except when the same data is encountered, where all the same data is ranked the same, and the rank is vacated between the last same record and the ranking of the next different record. Rank () is a jump sort, with two second names followed by a fourth place (also within each group).

Dense_rank ():
The Dense_rank function returns a unique value, except that when the same data is encountered, all the same data are ranked the same.
Dense_rank () is a sequential sort, with two second names still followed by third. The difference between him and Row_number is that Row_number has no duplicate values.

Here's an example:

"1" Test environment: SQL > Desc user_order;
Name Null? Type
-- --------------------------------------- -------- ----------------------------
region_id Number (2)
CUSTOMER_ID Number (2)
Customer_sales number

"2" test data: SQL > select * from User_order order by Customer_sales;

region_id customer_id Customer_sales
-- -------- ----------- --------------
5 1 151162
10 29 903383
6 7 971585
10 28 986964
9 21 1020541
9 22 1036146
8 16 1068467
6 8 1141638
5 3 1161286
5 5 1169926
8 19 1174421
7 12 1182275
7 11 1190421
6 10 1196748
6 9 1208959
10 30 1216858
5 2 1224992
9 24 1224992
9 23 1224992
8 18 1253840
7 15 1255591
7 13 1310434
10 27 1322747
8 20 1413722
6 6 1788836
10 26 1808949
5 4 1878275
7 14 1929774
8 17 1944281
9 25 2232703

Rows selected.

"3" row_number (), rank (), Dense_rank () the different instances of the three analytic functions

SQL > select region_id, customer_id, sum (customer_sales) Total,
2 rank () over (customer_sales) desc rank,
3 Dense_rank () over (order by sum (customer_sales) desc) Dense_rank,
4 row_number () over (order by sum (customer_sales) desc) row_number
5 from User_order
6 GROUP by region_id, customer_id;

region_id customer_id Total RANK dense_rank row_number
-- -------- ----------- ---------- ---------- ---------- ----------

8 18 1253840 11 11 11
5 2 1224992 12 12 12
9 23 1224992 12 12 13
9 24 1224992 12 12 14
10 30 1216858 15 13 15


Rows selected.

Note The green highlights above, which graphically illustrate 3 different ranking strategies:

① for the first record, 3 functions are ranked the same:

② when the second same record appears, rank and Dense_ Rank still gives the same rank of 12, and Row_number is postponed to 13, and so on to the third same record

③ when ranking to the next different record, you can see that the rank function vacated the rank of 13,14 between 12 and 15. Because the 2 rankings have actually been accounted for by the same second to third record. And Dense_rank is incremented sequentially. The Row_number function is also sequentially incremented

compared to the above 3 different strategies, we have to choose according to the customer's needs to decide:

① If the customer only needs to specify the number of records, then the use of Row_number is the simplest, But there's a danger of missing records.

② If the customer needs all records to reach the rank level, then rank or Dense_rank is a good choice. As for choosing which one to look at the needs of the customer, select Dense_rank or get the maximum record