One of the data Paging series: Oracle table data Paging retrieval SQL

About Oracle data paging retrieval SQL syntax, the network is everywhere, a variety of, this is also the author I search on the network of the more representative of the grammar, not my original, posted here, purely to let the "data page series" looks a little complete and plump some, it is a special statement here first , so as not to provoke a shout!

First, we introduce two more representative data paging SQL instance.

• No order by ordering. (Highest efficiency)

(After testing, this method is the lowest cost, nesting only one layer, the fastest!) Even if the amount of data retrieved is large and almost unaffected, the speed remains! )

SELECT * from(SELECTROWNUM asRowno, T.*       fromEMP TWHEREHire_datebetweenTo_date ('20060501','YYYYMMDD') andTo_date ('20060731','YYYYMMDD')           andROWNUM<=  -) Table_aliasWHERETable_alias.rowno>= Ten;

• There is an order by sort notation. (Highest efficiency)

(After testing, this method with the search scope expansion, the speed will be more and more slow Oh!) )

SELECT * from(SELECTTt.*, ROWNUM asRowno from(SELECTT.*              fromEMP TWHEREHire_datebetweenTo_date ('20060501','YYYYMMDD')                 andTo_date ('20060731','YYYYMMDD')                ORDER  byCreate_timeDESC, emp_no) TTWHEREROWNUM<=  -) Table_aliasWHERETable_alias.rowno>= Ten;

Referring to the above two instances, basically, the following SQL statement represents the regular paging retrieval format, according to the actual needs and personal proficiency in SQL, can be freely transformed to get their own needs of the paged SQL statement.

SELECT *  from (Select A.*, ROWNUM rn      from (select*from    where<=)where>=

The search for the most inner layer of SELECT * FROM table_name represents the original search statement without paging. ROWNUM <= 40 and RN >= 21 control The range of pages retrieved per page.

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

There are two ways to select the 21st to 40th record, one of which is shown in the above example in the second layer of the retrieval through the rownum <= 40来 control The maximum value, at the outermost control of the retrieval minimum. The other way is to remove the ROWNUM <= 40 statement that retrieves the second layer, controlling the minimum and maximum values of the paging at the outermost level of the retrieval. At this point, the search statement is as follows:

SELECT *  from (Select A.*, ROWNUM rn      from (select*from   TABLE_NAME) a)WHEREbetween and

Comparing these two formulations, the majority of cases, the first retrieval efficiency is much higher than the second one.

This is because in the CBO optimization mode, Oracle can push the outer retrieval condition into the inner search to improve the execution efficiency of the inner retrieval. For the first search statement, the second level of the search condition where ROWNUM <= 40 can be pushed into the internal retrieval by Oracle, so that if the result of Oracle retrieval exceeds the ROWNUM limit, the result is returned by terminating the retrieval.

And the second search statement, because the search condition between and 40 is present in the third level of the retrieval, and Oracle cannot push the third layer of the retrieval conditions to the most inner layer (even if pushed to the most inner layer is meaningless, because the most internal search does not know what RN represents). Therefore, for the second retrieval statement, the Oracle's inner layer is returned to the middle tier as all data that satisfies the criteria, and the middle tier returns to the outermost of all the data. The filtering of the data is done at the outermost layer, which is obviously much lower than the first one.

The above analysis is not only for simple retrieval of single table, but is as effective for the most inner retrieval, which is complex multi-table joint retrieval or the most inner-level retrieval containing sort.

Shangbing

Unit: Henan Electric Power Research Institute, Intelligent Grid

qq:52190634

Home: http://www.cnblogs.com/shangbingbing

Space: http://shangbingbing.qzone.qq.com

One of the data Paging series: Oracle table data Paging retrieval SQL