Oracle Paging Query and Principle Analysis (summary)

Oracle Paging Query and Principle Analysis (summary)

Oracle paging query is one of the most commonly used statements, in general, the company framework will provide just apply, for additions and deletions, check is one of the most critical and most difficult piece, which has the highest utilization of paging query. I rookie, the shortcomings are also hope that the great God a lot of guidance, thank you crossing!!!

Suppose we have a sys_user user table, specifically as follows:

Field	user_id	Login_name	Password	org_id	Create_time
Comments	User primary Key	Login Name	Password	Unit ID	Creation time

　　

--srcsql, Bottom sqlselect * from Sys_user t where t.org_id= ' 402881e54c40d74d014c40d8407a0016 ' ORDER by t.create_time Desc;

　　

　　1. Use between ... and ... (not recommended, slow in case of large data volume)
If you use between ... and ..., the bottom layer is again encapsulated into a data result set with line numbers, that is, the following SQL statement, Sqla is the lowest-level SQL.

--sql1.1select rownum num, tmp.* from (SRCSQL) TMP

　　Therefore, a result set with line numbers is obtained, and the result set is interval Shang.

Select *from (sql1.1) where num between 4 and 5

　　The full SQL display is:

SELECT * FROM  (select RowNum num, tmp.* from          (SELECT * from                  sys_user t                 where t.org_id = ' 402881e54c40d74d0 14c40d8407a0016 ' ORDER BY t.create_time Desc

) TMP) where num between 4 and 5

　　2, familiar with the line number generation mechanism, multi-layer nested paged query

First put the code after the reason, the shortcomings of the hope that more guidance Guide!!!

--sql2.1,,, because the line number rownum can only <=,<,!=,<> not >=,>,= (=1 is available), therefore, all the result sets are first bound to define the select RowNum num,tmp.* from ( SRCSQL) tmp where rownum<=5

The result set of the sql2.1 query is defined as a table and then the lower bound, so that the scope can be reduced gradually, the range is small, the amount of data is less, the query will certainly be faster!!!

SELECT * FROM (sql2.1) where num>2;

A total of three layers, do not know whether you understand??? , all of the SQL shows are:

SELECT * FROM  (select RowNum num, tmp.* from          (SELECT * from                  sys_user t                 where t.org_id = ' 402881e54c40d74d0 14c40d8407a0016 ' ORDER BY t.create_time Desc

) tmp where rownum <= 5) where num > 2;

　　Compare two methods

The first method is to isolate the global result set, and then to narrow the scope in the global result set, and the second is to scale down step-by-step until you get the result set of the demand ... It is not difficult to see the second method is more appropriate, in the case of large data volume faster ...

　　Principle Analysis

in both methods we used the line number rownum, for the Oracle line number, this is a dynamic variable, if you go to run the following SQL statement, you will find no results. Why?

SELECT * from Sys_user t where t.org_id= ' 402881e54c40d74d014c40d8407a0016 ' and  rownum>= ' 3 ' and rownum<= ' 5 ' ORDER BY t.create_time Desc;

　　RowNum is a pseudo-column (pseudo) in Oracle, which is intended to label line numbers for the resulting set of queries. You can implement the function of querying the first n rows (top-n), the middle rows (middle-n), and the last few lines (bottom-n). But RowNum is a very special column, the use of the process, first to understand the principles of Oracle RowNum, and then analyzed.

1.rownum can not directly precede the table name or alias, and so on. Other pseudo-columns are the same as Level,rowid.

2.rownum and where are in the same layer query, where conditions are compared using rownum, only <=,<,!=,<> can be used; >,>= (>=1,>=0,>0 and no effect ), = (use =, can only be where rownum=1 can, rownum>1 can not). Otherwise, no data is returned. If you use! = or <>, then just return to the front n-1 line, and the others are calculated according to the rownum principle.

3. When rownum and order by are used in a statement level (the same layer). See if the data for this query is fetched from the index (or ROWID and then positioned by the index), and the order of the fetches is consistent (note that the index can be obtained by its own desc), If not, then is the first query out, each row labeled RowNum, and then order by the results are reordered, then the order of rownum is chaotic, and is not sorted by the top-n results, but the first query top-n, and then sort

　　Java encapsulated SQL Paging query

The bottom-level SQL, the Srcsql in this article, first writes out all of the result sets that meet the criteria according to the requirements.

I am A J Java, below is my simple package, basically meet the majority of paging query needs.

/****srcsql Original Sql**pageno page **pagesize number per page **/private string Getpagequerysql (string srcsql, int pageno, int pageSize) {
   return "SELECT * FROM (select Tmp.*,rownum num from (", Srcsql, ") tmp where rownum <=" + pageno*pagesize + ") where Nu M > "+ (page-1) *pagesize;}

　　Happy moment

The roof of a building.
Woman: "Let's break up." ”
Man: "Why?" I love you so much? ”
Woman: "My father knew we had been against it, and you didn't know it, and now you won't let me go home!" ”
Man: "I believe our love can move your father's, you know, I really love you!" ”
Woman: "I know!" But we really can not be together, our love will not be blessed, even if my father agreed, waiting for my husband to come back, he will definitely fight with you. ”
Man: "My son what temper I will not know?" He doesn't have the guts! ”
Hey, this family!

Oracle Paging Query and Principle Analysis (summary)