Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle SQL statement to retrieve data paging table     - Linux Network Security: nmap port scanning software (Linux)

- Docker improve safety (Server)

- Hibernate Performance Optimization of EHCache cache (Programming)

- CentOS Set the Mono environment variable (Server)

- Compiled version of Android Opus audio codec library method (Programming)

- Manually create Oracle Database Explanations (Database)

- Laravel cache paged results (Server)

- C ++ precision performance test function (Programming)

- Linux novice common commands (Linux)

- Linux argument references and command substitution (Linux)

- 5 tips to improve your Linux desktop security (Linux)

- Oracle 11g to create a second instance on Linux (Database)

- CentOS boot image to achieve semi-automatic installation (Linux)

- Physical backup and recovery SYSTEM table space (Database)

- 5 interesting Linux command line tips (Linux)

- To share Linux script automatically change passwords (Linux)

- Install MATE desktop environment adjustment tools Mate Tweak 3.3.6 (Linux)

- Why HBase need to build SQL engine layer (Database)

- Arronax allows you to easily create desktop startup file (Linux)

- MySQL loose index scan (Database)

 
         
  Oracle SQL statement to retrieve data paging table
     
  Add Date : 2018-11-21      
         
         
         
  About the Oracle SQL syntax to retrieve data paging, network everywhere, many tricks herein, this is also the author himself on the web search more representative of grammar, not my original, posted here, is simply to allow the "Data tab series "looks a little fuller and complete, so the first statement about here especially, to avoid attracting condemning!

First introduced two more representative data paging to retrieve SQL instance.
• No ORDER BY sort of wording. (Most efficient)

(After testing, this method is the lowest cost, only one nesting fastest! Amount of data retrieved even bigger, almost unaffected, the speed is still!)

SELECT *
FROM (SELECT ROWNUM AS rowno, t. *
      FROM emp t
      WHERE hire_date BETWEEN TO_DATE ( '20060501', 'yyyymmdd')
AND TO_DATE ( '20060731', 'yyyymmdd')
          AND ROWNUM <= 20) table_alias
WHERE table_alias.rowno> = 10;

• There ORDER BY sort of wording. (Most efficient)

(Tested this method with the expansion of the search range, the speed will be more slow Oh!)

SELECT *
FROM (SELECT tt. *, ROWNUM AS rowno
      FROM (SELECT t. *
            FROM emp t
            WHERE hire_date BETWEEN TO_DATE ( '20060501', 'yyyymmdd')
                AND TO_DATE ( '20060731', 'yyyymmdd')
                ORDER BY create_time DESC, emp_no) tt
      WHERE ROWNUM <= 20) table_alias
WHERE table_alias.rowno> = 10;

Refer to the above two examples, basically, the following SQL statement on behalf of the conventional paging retrieval format, according to the actual needs and personal proficiency in SQL can be freely converted, to give the pagination SQL statement you need.

SELECT *
FROM (SELECT a. *, ROWNUM rn
      FROM (SELECT * FROM table_name) a
WHERE ROWNUM <= 40)
WHERE rn> = 21

Wherein the innermost retrieve SELECT * FROM TABLE_NAME means no flip the original search statement. ROWNUM <= 40 and RN> = 21 controls tab page range retrieval.

Given above this tab search statement, in most cases, it has a high efficiency. The purpose is to control the output of pagination result set size, the results are returned as soon as possible. Tab above the search statement, this consideration is mainly reflected in the WHERE ROWNUM <= 40 on the phrase.

There are two methods to select the first 21-40 record, one is shown in the above example to retrieve a second layer by ROWNUM <= 40 to control the maximum, minimum in the outermost layer of control to retrieve. The other way is to remove the second layer to retrieve WHERE ROWNUM <= 40 statement, minimum and maximum values ​​in the retrieval of the outermost control tab. In this case, the search statement is as follows:

SELECT *
FROM (SELECT a. *, ROWNUM rn
      FROM (SELECT * FROM table_name) a)
WHERE rn BETWEEN 21 AND 40

Compare these two way, the vast majority of cases, the first retrieval efficiency is much higher than the second.

This is because under the CBO optimization mode, Oracle can be pushed to the search condition outer inner search, in order to improve the efficiency of the inner search. For the first search statement, the second layer of the search condition WHERE ROWNUM <= 40 Oracle can be pushed into the inner search, so Oracle search results once more than ROWNUM restrictions on the termination of retrieval results returned.

And the second search statement, since the search condition BETWEEN 21 AND 40 are present in the retrieval of the third layer, and the third search condition Oracle can not be pushed to the innermost layer (even when pushed to the innermost layer has no meaning because innermost retrieval representatives do not know what RN). Thus, for the second retrieval statement, Oracle innermost layer is returned to the middle of all the data to meet the conditions, and the intermediate layer is returned to the outermost layer of all the data. Filtering data in the outermost layer is completed, it is clear that efficiency is much lower than the first retrieval.

The above analysis is not just for the simple search to retrieve a single table to retrieve the innermost layer is a complex multi-table retrieval or the joint innermost layer contains the sort of retrieval as effective.
     
         
         
         
  More:      
 
- Android first line of code study notes (Programming)
- CentOS yum configuration under local sources (Linux)
- C ++ virtual functions Classic Insights (Programming)
- Oracle Database asynchronous IO cause slow query response (Database)
- Vim custom color (Linux)
- Java objects to garbage collection (Programming)
- Ubuntu 14.04 virtual machine switching desktop environments (Linux)
- Installation Yarock 1.1.4 Music Player in Ubuntu (Linux)
- Justniffer installed on Ubuntu 15.04 (Linux)
- Ubuntu Thunderbird 24.4.0 (Linux)
- Network Security: SYN attacks against under linux (Linux)
- HTML5 Application Cache (Programming)
- Ubuntu 10.10 install Oracle 10g graphic tutorials (Database)
- MySQL5.6.12 Waiting for commit lock lead to hang from the library housing problem analysis (Database)
- In-depth summary of the PHP core of object-oriented (Programming)
- Restore database fault encountered ORA-0600 (Database)
- Netapp storage routine inspections and information gathering (Linux)
- iOS developers - a simple factory pattern and OC reflection (Programming)
- C ++, overloading, cover, hide (Programming)
- Installation JDK 1.8 under CentOS7 (Linux)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.