Oracle Large Data Paging query

(i) Paging implementation and performance

Oracle Paging query statements can basically be applied in the format given in this article.

Paging Query format:

SELECT * FROM
(
SELECT a.*, RowNum RN
From (SELECT * from table_name) A
WHERE RowNum <= 40
)
WHERE RN >= 21

The most inner query select * FROM table_name represents the original query statement without paging. RowNum <= 40 and RN >= 21 control the scope of each page of the paging query.

The paging query statement given above is highly efficient in most cases. The purpose of paging is to control the size of the output result set and return the result as quickly as possible. In the paging query above, this consideration is mainly reflected in the sentence where RowNum <= 40.

There are two ways to select the 21st to 40th record, one of which is shown in the above example to control the maximum by RowNum <= 40来 in the second layer of the query, which controls the minimum value at the outermost level of the query. The other way is to remove the query's second-level where rownum <= 40 statement, which controls the minimum and maximum paging values at the outermost edge of the query. This is the query statement as follows:

SELECT * FROM
(
SELECT a.*, RowNum RN
From (SELECT * from table_name) A
)
WHERE RN BETWEEN and 40

In contrast to both of these, the first query is much more efficient than the second one in most cases.

This is because in the CBO optimization mode, Oracle can push the outer query criteria into the inner query to improve the efficiency of the inner query execution. For the first query, the second-level query condition where rownum <= 40 can be pushed into an inner query by Oracle, so that once the results of the Oracle query exceed the rownum limit, the query terminates the result.

The second query, because the query conditions between and 40 are present in the third layer of the query, and Oracle cannot push the third-tier query condition to the very inside (even if it doesn't make sense to push to the inner layer, because the most inner query doesn't know what the RN represents). Therefore, for the second query statement, the Oracle is returned to the middle tier with all the data that satisfies the condition, and the middle tier returns to the outermost and all data. The filtering of data is done at the outermost layer, which is obviously much less efficient than the first query.

The query that is analyzed above is not just a simple query for a single table, but is as effective for the most inner query as a complex multiple-table union query or for the most inner-level query that contains the sort.

There is no description of the query that contains the sort, and the next article is illustrated with an example. Here's a brief discussion of multiple-table syndication. For the most common table of magnitude join queries, the CBO may typically use two ways to connect nested loop and hash join (the efficiency of MERGE join is less efficient than hash join, which is not considered by the general CBO). Here, because paging is used, a maximum number of records is specified, and the NESTED loop can stop immediately and return the result to the middle tier when the number of records exceeds the maximum, and the hash join must process all the result sets (the MERGE join also). In most cases, it is more efficient to select the nested loop as a query connection method for paging queries (most of the time when paging queries are the data from the previous pages, the more the number of pages accessed later).

Therefore, if you don't mind using hint in your system, you can rewrite the paging query to:

SELECT/*+ first_rows * * FROM
(
SELECT a.*, RowNum RN
From (SELECT * from table_name) A
WHERE RowNum <= 40
)
WHERE RN >= 21

(ii) Oracle top n

SELECT a.*, RowNum RN
From (SELECT * from table_name) A
WHERE RowNum <= 40

The above is the Oracle implementation top N function

SELECT a.*, RowNum RN
From (SELECT * from table_name) A
WHERE rownum between 2 and 100

Always return empty records

Reason:

For RowNum, it is an Oracle system sequence that is assigned the number of rows returned from the query, the first row is assigned 1, the second row is 2, and so on, this pseudo field can be used to limit the number of rows returned by the query, and rownum cannot be prefixed with the name of any table.
An example is provided:
For example, table: Student (Student) table, table structure is:
ID Char (6)--School number
Name VARCHAR2 (10)--Name
CREATE TABLE Student (ID char (6), name VARCHAR2 (100));
INSERT into sale values (' 200001 ', ' Zhang Yi ');
INSERT into sale values (' 200002 ', ' King II ');
INSERT into sale values (' 200003 ', ' lie triple Systems ');
INSERT into sale values (' 200004 ', ' Zhao Si ');
Commit
(1) rownum for the query condition equal to a value
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; (You can use a limit to return the number of records to ensure no errors, such as: implicit cursors)
Sql> Select Rownum,id,name from student where rownum=1;
RowNum ID NAME
---------- ------ ---------------------------------------------------
1 200001 Sheets A
Sql> Select Rownum,id,name from student where rownum = 2;
RowNum ID NAME
---------- ------ ---------------------------------------------------
(2) RowNum for query conditions greater than a value
If you want to find a record from the second row, when you use ROWNUM&GT;2, you cannot find the record because rownum is a pseudo column that always starts with 1, and Oracle thinks rownum> N (the natural number of n>1) is still not tenable, So I can't find the records.
Sql> Select Rownum,id,name from student where RowNum >2;
RowNum ID NAME
---------- ------ ---------------------------------------------------
How can we find the record after the second line? You can use the following subquery methods to resolve. 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;
NO ID NAME
---------- ------ ---------------------------------------------------
3 200003 Lie triple systems
4 200004 Zhao Si
Sql> SELECT * FROM (select Rownum,id,name from student) where rownum>2;
RowNum ID NAME
---------- ------ ---------------------------------------------------
(3) RowNum for a query condition that is less than a value
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;
RowNum ID NAME
---------- ------ ---------------------------------------------------
1 200001 Sheets A
2 200002 King Two
In a few cases, may sometimes need to query rownum in a certain interval of data, then how to do it from the above can be seen rownum to less than a value of the query condition is artificial true, rownum for more than a value of the query conditions are directly considered false, But it can be indirectly turned into the view that it is true. Then you must use subqueries. For example, to query rownum between the second and third rows of data, including the second and third rows of data, we can only write the following statement, let it return a row of records less than or equal to three, and then in the main query to determine the new rownum is greater than or equal to two of the record row. But such operations can affect speed in large data sets.
Sql> SELECT * FROM (select RowNum no,id,name from student where rownum<=3) where no >=2;
NO ID NAME
---------- ------ ---------------------------------------------------
2 200002 King Two
3 200003 Lie triple systems
(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
This is sorted by name, and the correct ordinal number is marked with rownum (small to large)

---above for rownum, in order to better use RowNum lay the foundation.

In fact, the key to understanding rownum is how Oracle executes query statements. If you perform a Cartesian set operation and then execute a where condition limit, then rownum can implement rownum> N (n>=1) functionality. But Oralce is the side of the execution of Cartesian set operations, edge application selection criteria, so Rownum>n (n>1=) never set up (user comments)