Home > database > Oracle paging query and solution
Oracle paging query and solution
Time：10-23
1. The ROWNUM: Concept: using ROWNUM OORACLE is a pseudo columns, database extract records will generate value of 1, 2, 3, 4 function is used to implement the ORACLE paging must use order to check for
Query execution process conditions (with) : a. EMPLOYEES table, generate EMOLYEES pseudo column B. according to whether the ROWNUM paging conditions and the conditions of matching C. condition matching, remove this condition D. generate a second ROWNUM replicated r
2. The problem of the alias Because ROWNUM WHERE judgment to execute before the SELECT key words, the current in the query ROWNUM alias cannot be used in conditions do judgment, alias can use only one external condition judgment
3. The ROWID is the database records generated when real physical address, the only constant Role: database operation record use Index - ROWID - ROWID to calculate into a row of physical address to get a row Such as: extraction of the first three lines of the staff table data
Such as: extraction after four rows of data Error example: SELECT ROWNUM, EMP. * FROM EMP WHERE ROWNUM & gt; 3 -- error Generated the first ROWNUM, the condition judgment is not accord with, can't extract as a result, the result is NULL Solution: pseudo table the SELECT ROWNUM, ROWNUM query belt first EMP. * FROM EMP query table, choose four data after the SELECT * FROM (SELECT ROWNUM R, EMP. * FROM EMP) RE the WHERE RE. R & gt; 3
1. Extract the wages of the top three employees Although the query results, but does not seem to be former employees wages of the top three, Cause analysis: the execution of the WHERE condition before ORACLE BY Mr Page is a ROWNUM before, obviously is a serial number has been generated when good The right is the SELECT * FROM EMP ORACLE BY SALARY DESC sorting first, SELECT ROWNUM, e. * FROM (SELECT * FROM EMP ORACLE BY SAL DESC) E WHERE ROWNUM BETWEEN 1 AND 4
2. The extraction of the recorded data efficiency of 6 to 10 I want to extract the data is the data before 6 to 10, the data is not required to filter out, improve the efficiency of writing, only to extract the paging (table data is efficiency will be very low), As shown in figure,
CodePudding user response:
Summary is quite good!
CodePudding user response:
Added to the 100, good for some people to come over,
CodePudding user response:
Ask a question
The following statement is how to optimize the seconds out (assumption: the emp table tens of millions, satisfy the deptno conditions, five million) : SELECT * FROM (SELECT rownum rn, emp. * FROM emp WHERE DEPTNO='DEVELOPER' ORACLE BY SAL DESC) WHERE rownum & lt;=20
CodePudding user response:
Didn't notice that, copy, after the where clause for ORACLE BY should be ORDER BY...
