Tips on Oracle Database paging Query

There are many common methods for Oracle-related optimization, as we all know, such as: select * is not recommended for use, and the efficiency is very poor. It is recommended to explicitly retrieve columns, even all fields should be listed. Count (*) is used when the total number is obtained. To improve the cache hit rate, try to reuse SQL statements as much as possible. In addition, a good choice for improving performance may be to put all the character data

There are many common methods for Oracle-related optimization, as we all know, such as: select * is not recommended for use, and the efficiency is very poor. It is recommended to explicitly retrieve columns, even all fields should be listed. Count (*) is used when the total number is obtained. To improve the cache hit rate, try to reuse SQL statements as much as possible. In addition, a good choice for improving performance may be to put all the character data

There are many common methods for Oracle-related optimization, as we all know, such as: select * is not recommended for use, and the efficiency is very poor. It is recommended to explicitly retrieve columns, even all fields should be listed. Count (*) is used when the total number is obtained. To improve the cache hit rate, try to reuse SQL statements as much as possible. In addition, a good choice for improving performance may be to save all the character data as Unicode, and Java processes all the data in the form of Unicode. Therefore, the database driver no longer needs to execute the conversion process.

In this article, we will mainly understand many implementation methods for implementing paging using SQL. Some statements may not be very common and can only be used in some special scenarios, however, despite its powerful functions and ease of use, the defect is that the efficiency may slow down. Next, IT male experienced development experience, explain the functions of these commonly used SQL query statements separately. I hope it will help you!

The following describes three general implementation schemes,ROWNUMIs a core keyword. It is a virtual column during query. The value ranges from 1 to the total number of records.

1. First, we will introduce one of the most commonly used implementation methods in our work:

SELECT *FROM (SELECT IT_NAN_.*, ROWNUM R_  FROM (SELECT *       FROM IT_NAN T       ORDER BY T.IT_CAT) IT_NAN_  WHERE ROWNUM <= 50000) WHERE R_ >= 49991;

The inmost query SELECT statement is the original query statement without paging. you can replace it with any of your Select SQL statements; ROWNUM <= 50000 and ROWNUM> = 49991 control the range of each page of the paging query. The purpose of paging is to control the size of the output result set and return the results as soon as possible. The preceding SQL statement has a high efficiency in most cases, mainly reflected in the WHERE ROWNUM <= 50000 sentence, this controls the maximum number of records in the query process. (After testing millions of data in this IT male, the above SQL test time lingers around 50000 5s, and the time increases slightly as the value of increases .)

2. In the preceding example, the maximum value is controlled by ROWNUM <= 50000 on the second layer of the query, and the minimum value is controlled at the outermost layer of the query. The other method is to remove the where rownum <= 50000 Statement on the second layer of the query, and control the minimum and maximum paging values at the outermost layer of the query. The SQL statement is as follows, that is, the second implementation method to be introduced:

SELECT *FROM (SELECT IT_NAN_.*, ROWNUM R_  FROM (SELECT *    FROM IT_NAN T    ORDER BY T.IT_CAT) IT_NAN_ )WHERE R_ BETWEEN 49991 AND 50000;

Since Oracle can push the outer query condition to the inner query, it can improve the execution efficiency of the inner query, but it cannot span multiple layers.
For the first query statement, the query condition where rownum <= 50000 on the second layer can be pushed to the internal query by Oracle. In this way, once the query result of Oracle exceeds the ROWNUM limit, the query is terminated and the result is returned.

The second query statement, because the query conditions BETWEEN 49991 AND 50000 exist on the third layer of the query, oracle cannot push the layer-3 query conditions to the innermost layer (even pushing to the innermost layer makes no sense, because the innermost layer query does not know what ROWNUM represents ). Therefore, for the second query statement, the oldest layer of Oracle returns all the data that meets the conditions to the middle layer, and the data that the middle layer returns to the outermost layer is all the data. Data filtering is completed at the outermost layer. Obviously, this efficiency is much lower than the first query. (When this query statement is tested by an IT male and the data above, the test time is about twice the time of the first query, and also increases with the value of 50000, time increases slightly .)

3. the preceding two solutions are completely completed using ROWNUM. The following method combines ROWID and ROWNUM. The SQL statement is as follows:

SELECT *FROM (SELECT RID  FROM (SELECT IT_NAN_1.RID, ROWNUM R_    FROM (SELECT T.ROWID RID      FROM IT_NAN T      ORDER BY T.IT_CAT) IT_NAN_    WHERE ROWNUM <= 50000)  WHERE R_ >= 49991) IT_NAN_1_ , IT_NAN IT_NAN_2_WHERE IT_NAN_1_.RID = IT_NAN IT_NAN_2_.ROWID ;

From the statement point of view, a total of four-layer Select queries are nested, the innermost layer is the replacement of the original non-Paging SQL statement, but the field queried by him is only ROWID, there are no actual table fields to be queried. The actual field values are implemented at the outermost layer.

The principle of this method is roughly as follows: first, the ROWID of the 10 actually returned records after the page is queried through ROWNUM, and finally the finally returned field value is queried and returned through ROWID;

Compared with the preceding two implementation methods, the SQL implementation method is more complex and less universal, because the original query statement must be divided into two parts (the query field is at the outermost layer, the table and its query conditions are in the innermost layer );
However, this kind of implementation is advantageous in specific scenarios: for example, we often need to flip pages to the very back, for example, we often need to query 100000-records and later data; this solution may be more efficient than the previous one;
In the preceding scheme, ROWNUM <= 91000 is used for control. In this way, 91000 pieces of data need to be queried, and then the data between the last 90000-91000 is obtained, this scheme uses ROWID to retrieve the required 100 pieces of data;

From the perspective of constantly turning back the page, the cost of the first implementation scheme is getting higher and higher, basically linear growth, and the cost of the third scheme is not as fast as that of the former, the increase is only reflected in the reading of ROWID through the query condition. (This query statement is also tested by the IT male. The test time is about half of the first query time, and with the value of 50000 increasing, the effect will be more impressive. Then I tested the query speed of 1-10 pages, and found that this query method is still nearly half faster than the first one... I don't know the specific reason, but it's better than words ...)

This query method is really powerful. If hibernate's second-level cache is applied to your project to cache the object, the efficiency will be more impressive.

4. Of course, in addition to the above solutions, we can also use the following SQL:

SELECT *FROM IT_NANWHERE ROWID NOT IN(SELECT ROWID FROM IT_NAN WHERE ROWNUM <= 49990)AND ROWNUM <= 10;SELECT *FROM IT_NANWHERE ROWNUM <= 50000MINUSSELECT * FROM IT_NAN WHERE ROWNUM <= 49990

The above two query statements are also tested by the IT male, which is about 8 s. Therefore, if the project data volume is small, you can try to use it. If the data volume is larger than one million, consider the above.

..................
Note: When ROWNUM is used as the query condition, it is executed before order by, so be careful;
For example, the following SQL statement cannot be used to query the first 10 records in IT_NAN in reverse order by IT_CAT:

SELECT * FROM IT_NAN T WHERE ROWNUM <= 10 ORDER BY T.IT_CAT DESC;

Of course, for this requirement, you can refer to the subquery in the example above for implementation, or you can use the Oracle sorting function: ROW_NUMBER () the function generates a new data Column Based on the OrderID column. Of course, if you are interested in ROW_NUMBER, you can ask du Niang or Gu Ge. I will not elaborate on it here. The effect is similar.

There are still many things to do about Data Optimization. We need to consider it and study it. This is just a little bit of tips. I will continue to share relevant experience in my blog if I have time, of course, I also hope that you will be able to make corrections and make slack shots, discuss and share your optimization experience, and grow together!

Original article address: Tips for querying Oracle databases by page. Thank you for sharing the original article.