Analysis of rownum usage in Oracle

Note: rownum starts from 1; 1. rownum sorts records according to the order when inserting records, so be sure to pay attention when there is an order by clause! 2. When the rownum and order by fields are used, what is the impact of the primary key? 3. What sequence does the subquery use for rownum rn and rn for external queries? 4. If the id Primary Key is inserted in ascending order, and the select statement does not have the group by and order by clauses, the rownum order is basically the same as the id order. For the rownum problem in Oracle, many documents do not support>, >=, =, between... and. The preceding symbols can only be used (<, <= ,! =), Not to mention>,> =, =, .. and will prompt SQL syntax errors, but often cannot find a record, and it seems to be an inexplicable result, in fact, you should not be surprised to understand the meaning of this rownum pseudo column. It is also a pseudo column. rownum and rowid can be somewhat different. The following example assumes that a table t1 (c1) there are 20 records. If select rownum, c1 from t1 where rownum <10 is used, as long as the number is smaller than the number, it is easy to reach an agreement with the general understanding in terms of concept, there should be no doubt. If you use select rownum, c1 from t1 where rownum> 10 (if you write such a query statement, you should want to get the next 10 records in the table in your mind ), you will find that the displayed results will disappoint you. Maybe you will doubt that no one has deleted some records and then check the number of records. Is it still 20? Where is the problem? First, understand the meaning of rownum. Because ROWNUM is a pseudo column added to the result set, that is, a column added after the result set is found (emphasize: a result set must be created first ). In short, rownum is the serial number that matches the condition result. It always starts from 1. Therefore, the result you select cannot be less than 1, and there are other values greater than 1. Therefore, you cannot expect the following result set: 11 aaaaaaaaaa 12 bbbbbbb 13 ccccccccc ................. rownum> 10 does not have a record. If the first row does not meet the requirements, the ROWNUM of the second row becomes 1, so there will never be a record that meets the conditions. It can be understood as follows: ROWNUM is a sequence in which oracle databases read data from data files or buffers. If it obtains the first record, the rownum value is 1, the second record is 2, and so on. If you use>,> =, =,... and, because the rownum of the first record obtained from the buffer or data file is 1, it is deleted and then removed. However, its rownum is still 1 and deleted, and so on. With the concept of rownum established from different aspects above, we can understand several existing images of rownum. 1. select rownum, c1 from t1 where rownum! = 10 why is the first nine data records returned? It is the same as select rownum, c1 from tablename where rownum <10 returned result set? Because after the result set is queried and 9th records are displayed, all subsequent records are displayed! = 10, or> = 10, so only the first nine records are displayed. It can also be understood that the value of rownum is 10 for the record after rownum is 9, because the condition is! = 10, so remove it. Then add the record, and the rownum is 10. If so, only the first nine records will be displayed. 2. Why can't I find a record when rownum> 1, while rownum> 0 or rownum> = 1 shows all the records? Because rownum is added after the queried result set, it always starts from 1. 3. Why can't between 1 and 10 or between 0 and 10 be found, but between 2 and 10 can be used? The reason is the same as above, because rownum always starts from 1. It can be seen from the above that it is wrong to discard the rownum = 1 record at any time, and it is indispensable in the result set. If rownum = 1 is missing, it cannot exist like a castle in the air, therefore, your rownum condition must include 1. However, if you want to use rownum> 10, you need to use nested statements to convert Mr. rownum into and then query it. Select * from (selet rownum as rn, a. * from t1 a where...) where rn> 10 and... this is what we do in the code. In addition, both rowid and rownum are called pseudo columns, but they exist in different ways. rowid can be physical, indicating the unique location ID of the record in the tablespace, it is unique in DB. As long as the record has not been moved, the rowid remains unchanged. Compared with a table, rowid is similar to a common column in a table. Therefore, if rowid is used as a condition, rownum does not occur. In addition, you must note that rownum cannot use any base table name as the prefix. The following is an excerpt from "parsing oracle rownum". For rownum, it is the number allocated by the oracle System to the row returned from the query in sequence. The first row returned is 1, and the second row is 2, this pseudo field can be used to limit the total number of rows returned by the query, and rownum cannot use any table name as the prefix. (1) rownum: If you want to find the information of the first student in the student table that is equal to a certain value, you can use rownum = 1 as the condition. However, if you want to find the second student information in the student table, you cannot find the data using rownum = 2. Because rownum starts from 1, but the natural numbers above 1 are regarded as false when rownum is equal to or equal to the true value. Therefore, rownum = n (The Natural Number of n> 1) cannot be found ). SQL> select rownum, id, name from student where rownum = 1, id, name from student where rownum = 2; rownum id name ---------- ------ --------------------------------------------------- (2) rownum for query conditions greater than a value if you want to find records from the second line, when rownum> 2 is used, the record cannot be found because rownum is a pseudo column always starting from 1. Oracle considers rownum> n (Natural Number of n> 1) this condition is still not true, so no records can be found. You can use the following subquery method to locate records after the second row. Note that the rownum in the subquery must have an alias; otherwise, the record is not found because rownum is not a column of a table. If the alias cannot be found, you cannot know whether rownum is a subquery column or a primary query column. SQL> select * from (select rownum no, id, name from student) where no> 2; NO ID NAME ---------- ------------------------------------------------- 3 200003 Li San 4 200004 Zhao Si (3) rownum is true for rownum <n (Natural Number of n> 1), which is a query condition smaller than a certain value. Therefore, records can be found. SQL> select rownum, id, name from student where rownum <3; ROWNUM ID NAME ---------- rows 1 200001 Zhang Yi 2 200002 Wang 2 query rownum data in a certain range, you must use a subquery. For example, to query the data of rownum between the second row and the third row, including the data of the second row and the third row, we can only write the following statement to first let it return the record rows smaller than or equal to three, then, in the primary query, it is judged that the alias column of the new rownum is greater than or equal to two record rows. However, such operations will affect the speed in the big data set. SQL> select * from (select rownum no, id, name from student where rownum <= 3) where no> = 2; no id name ---------- ------ ------------------------------------------------- 2 200002 Wang 2 3 200003 Li San (4) rownum and the rownum in Oracle are generated when data is retrieved, therefore, you must pay attention to the specified rowmun rows of data to be sorted. SQL> select rownum, id, name from student order by name; ROWNUM ID NAME ---------- ------ --------------------------------------------------- 3 200003 Li San 2 200002 Wang 2 1 200001 Zhang Yi 4 200004 Zhao Si can see, rownum is not the sequence number generated by the name column. The system assigns the number of the record row according to the sequence in which the record is inserted, and the rowid is also allocated sequentially. To solve this problem, subqueries must be used. SQL> select rownum, id, name from (select * from student order by name ); rownum id name ---------- ------ ------------------------------------------------- 1 200003 Li San 2 200002 Wang 2 3 200001 Zhang Yi 4 200004 Zhao Si this way becomes by name, and mark the correct sequence number with rownum (small to big) in my work, I have a table with millions of records. On the jsp page, we need to display the table by page, so we can use rownum for it, the following is a specific method (20 entries are displayed on each page): "select * from tabname where rownum <20 order by name", but it is found that oracle cannot be executed as needed, Instead, take 20 records at will first, and then order by. After consulting oracle, it is said that rownum is indeed like this. If you want to use it, you can only use subqueries to sort the records first, then rownum, the method is as follows: "select * from (select * from tabname order by name) where rownum <20", but the efficiency will be much lower. After the author's experiment, we only need to add a primary key or index to the order by field to let oracle first sort by this field, and then rownum; the method remains unchanged: "select * from tabname where rownum <20 order by name" gets the nth row in a column select column_name from (select table_name. *, dense_rank () over (order by column desc) rank from table_name) where rank = & N; if you want to return the first five records: select * from tablename where rownum <6; (or rownum <= 5 or rownum! = 6) If you want to return records 5-9: select * from tablename where... And rownum <10 minus select * from tablename where... And rownum <5 order by name: The result is sorted by name to display the result. (Select first and then sort) Note: Only the above symbols can be used (<, <= ,! = ). Select * from tablename where rownum! = 10; the first nine records are returned. Cannot be used:>,> =, =, Between... and. Since rownum is a pseudo column that always starts from 1, Oracle does not think this condition is true. In addition, this method is faster: select * from (select rownum r, a from yourtable where rownum <= 20 order by name) where r> 10 to retrieve the 11-20 records! (Select first and then sort again) to sort and then select, you must use select nesting: the outer layer of the inner layer of sorting is selected. Rownum is generated with the result set. Once generated, it will not change. At the same time, the generated results are incremental in sequence, and there will never be 2 without 1! Rownum is a pseudo column generated when the query set is generated. If the where condition contains the rownum condition, then: 1: If the condition is a constant, then: only rownum = 1, <= a natural number greater than 1, = A number greater than 1, there is no result; if the number is greater than one, there is no result. That is, when a rownum does not meet the condition, the query ends. this is the stop key. (if one does not meet the condition, the system filters out the record, then the rownum of the next record is still this, so the subsequent records will no longer meet, this is stop key); 2: If the judgment value is not a constant, then: if the condition is = var, the condition is met only when var is 1. In this case, the stop key does not exist. full scan is required to determine each data that meets other where conditions, select a row before selecting rows with rownum = 2 ...... The following is an excerpt from China IT lab. 1. in ORACLE, select top n is implemented. Because ORACLE does not support select top statements, order by and ROWNUM are often used in ORACLE to query select top n. To put it simply, the implementation method is as follows: SELECT column name 1... column name n FROM (SELECT column name 1... column name n FROM table name order by column name 1... column name n) where rownum <= N (number of Records extracted) order by rownum asc the following example briefly describes. The customer (id, name) Table has the following data: id name 01 first 02 Second 03 third 04 forth 05 th 06 sixth 07 seventh 08 eighth 09 ninth 10 tenth 11 last extract the SQL statements of the first three customers by NAME as follows: SELECT * FROM (SELECT * from customer order by name) where rownum <= 3 order by rownum asc output result: id name 08 eighth 05 th 01 first 2. in the top n records, the M (M <= N) record ROWNUM is a hidden sub-segment of the Data number in the record table, therefore, we can extract the ROWNUM of the top n records at the same time, and then extract the records numbered M from the N records. Even if we want to get the results. From the above analysis, you can easily obtain the following SQL statement. SELECT column name 1... column name n FROM (select rownum recno, column name 1... column name nFROM (SELECT column name 1... column name n FROM table name order by column name 1... column name n) where rownum <= N (number of Records extracted) order by rownum asc) where recno = M (M <= N) based on the data of the above tables, the SQL statement that obtains the information of the second customer in alphabetical order of the NAME should be written as follows: SELECT ID, NAME FROM (SELECT ROWNUM RECNO, ID, name from (SELECT * from customer order by name) where rownum <= 3 order by rownum asc) where recno = 2 the result is: id name 05 th 3. Extract the N records in the record set sorted in a certain way in the description of 2. When M = N, it is the result of our title. In fact, the N> M data in the 2 approach is basically not used. We just use it to illustrate convenience. As described above, the SQL statement should be: SELECT column name 1... column name n FROM (select rownum recno, column name 1... column name nFROM (SELECT column name 1... column name n FROM table name order by column name 1... column name n) where rownum <= N (number of Records extracted) order by rownum asc) where recno = N, then the SQL statement in example 2 is: SELECT ID, name from (select rownum recno, ID, name from (SELECT * from customer order by name) where rownum <= 2 order by rownum asc) where recno = 2 the result is: id name 05 th 4. extract the MTH record from the record set sorted in a certain way In the X records 3 section, we only extract one record. When we need to extract multiple records, at this time, the value of N in 2 should be in the range of N> = (M + X-1). It is time to make the most economical value equal to the value. Of course, the final extraction condition is not RECNO = N, it should be recno between m and (M + X-1), so the following SQL statement is: SELECT column name 1... column name n FROM (select rownum recno, column name 1... column name nFROM (SELECT column name 1... column name n FROM table name order by column name 1... column name n) where rownum <= N (N> = (M + X-1) order by rownum asc) where recno between m and (M + X-1) take the preceding data as an example. The SQL statement for extracting the three records starting FROM the first 2nd records with the NAME letter is: SELECT ID, NAME FROM (SELECT ROWNUM RECNO, ID, name from (SELECT * FROM CUSTOM Er order by name) where rownum <= (2 + 3-1) order by rownum asc) where recno between 2 AND (2 + 3-1) The result is as follows: id name 05 th 01 first 04 forth is based on this. If you expand it to a stored procedure, you can easily extract data by page by setting the number of records and the number of records to be extracted. Of course, the above are some of the most basic ones, which are often not so simple in actual applications. However, no matter how complicated applications are, they are always composed of these simple elements, it is always important to master some basic methods. Author sangei