Rownum, Rowid, row_number (), and oracle paging query details, rownumrowid

Rownum, Rowid, row_number (), and oracle paging query details, rownumrowid

1. Both rownum and rowid are pseudo columns, but they are fundamentally different. rownum assigns

Logical numbers and different SQL statements will lead to different rownum, but rowid is physically structured. When each record is inserted into the database,

There will be a unique physical record. Rowid can be said to exist in the house, indicating a unique location ID of the record in the tablespace, which is unique in the DB. As long as the record has not been moved, the rowid remains unchanged. Rowid is equivalent to a common column relative to a table.

Rownum is commonly used for paging queries, while rowid is often used to remove duplicate records.

2. rownum is a pairResult setA pseudo column added, that is, a column added after the result set is found (Result set first). In short, rownum isSerial number of the matching result. It always starts from 1, so the selected result cannot contain 1, and there are other values greater than 1.

It can also be understood that rownum is the sequence in which oracle databases read data from data files or buffers. If it obtains the first record, rownum is 1 and 2nd is 2. If you use>,> =, =,... and, because the rownum of the first record obtained from the buffer or data file is 1, it is deleted and then removed. However, rownum is still 1 and deleted, no data.

Case1:

    SQL> select * from emp where rownum between 2 and 5;      EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO      ----- ---------- --------- ----- ----------- --------- --------- ------  

Case2:

    SQL> select * from emp where rownum<>3;      EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO      ----- ---------- --------- ----- ----------- --------- --------- ------       7369 SMITH      CLERK      7902 1980/12/17     800.00               20       7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30  

Case3:

    SQL> select * from emp where rownum>0;      EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO      ----- ---------- --------- ----- ----------- --------- --------- ------       7369 SMITH      CLERK      7902 1980/12/17     800.00               20       7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30       7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30       7566 JONES      MANAGER    7839 1981/4/2      2975.00               20       7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30       7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30       7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10       7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20       7839 KING       PRESIDENT       1981/11/17    5000.00               10       7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30       7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20       7900 JAMES      CLERK      7698 1981/12/3      950.00               30       7902 FORD       ANALYST    7566 1981/12/3     3000.00               20       7934 MILLER     CLERK      7782 1982/1/23     1300.00               10      14 rows selected  

Case4:

    SQL> select * from emp where rownum between 1 and 3;      EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO      ----- ---------- --------- ----- ----------- --------- --------- ------       7369 SMITH      CLERK      7902 1980/12/17     800.00               20       7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30       7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30  

3. rownum and row_numbe () over:

When rownum is used for sorting, a pseudo-column rownum is added to the result set before sorting.

Sort the clause before calculating the row number.

Case:

Order:

    SQL> select a.*,rownum from emp a order by a.ename;      EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO     ROWNUM      ----- ---------- --------- ----- ----------- --------- --------- ------ ----------       7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20         11       7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30          2       7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30          6       7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10          7       7902 FORD       ANALYST    7566 1981/12/3     3000.00               20         13       7900 JAMES      CLERK      7698 1981/12/3      950.00               30         12       7566 JONES      MANAGER    7839 1981/4/2      2975.00               20          4       7839 KING       PRESIDENT       1981/11/17    5000.00               10          9       7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30          5       7934 MILLER     CLERK      7782 1982/1/23     1300.00               10         14       7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20          8       7369 SMITH      CLERK      7902 1980/12/17     800.00               20          1       7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30         10       7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30          3      14 rows selected  

Row_number () over (order)

    SQL> select a.*,row_number() over(order by a.ename) from emp a;      EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO ROW_NUMBER()OVER(ORDERBYA.ENAM      ----- ---------- --------- ----- ----------- --------- --------- ------ ------------------------------       7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20                              1       7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30                              2       7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30                              3       7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10                              4       7902 FORD       ANALYST    7566 1981/12/3     3000.00               20                              5       7900 JAMES      CLERK      7698 1981/12/3      950.00               30                              6       7566 JONES      MANAGER    7839 1981/4/2      2975.00               20                              7       7839 KING       PRESIDENT       1981/11/17    5000.00               10                              8       7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30                              9       7934 MILLER     CLERK      7782 1982/1/23     1300.00               10                             10       7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20                             11       7369 SMITH      CLERK      7902 1980/12/17     800.00               20                             12       7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30                             13       7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30                             14      14 rows selected  

4.

Case1: controls the minimum and maximum paging values at the outermost layer.

    SELECT *        FROM (SELECT A.*, ROWNUM RN                FROM GAS_CA.A_CARD_METER_PAY A               WHERE A.PURCH_GAS_TIME BETWEEN TO_DATE('2015/1/1', 'YYYY/MM/DD') AND                     TO_DATE('2016/1/1', 'YYYY/MM/DD')                 AND A.ORG_NO LIKE '1009%')       WHERE RN BETWEEN 10 AND 20;  

Case2:

    SELECT *        FROM (SELECT ROWNUM RN, A.*                FROM GAS_CA.A_CARD_METER_PAY A               WHERE A.PURCH_GAS_TIME BETWEEN TO_DATE('2015/1/1', 'YYYY/MM/DD') AND                     TO_DATE('2016/1/1', 'YYYY/MM/DD')                 AND A.ORG_NO LIKE '1009%'                 AND ROWNUM <= 20)       WHERE RN >= 10;  

For case1 and case2, case2 is much more efficient than the first query in most cases.

In the CBO optimization mode, oracle can push the outer query conditions to the inner query to improve the execution efficiency of the inner query. For the second statement,

The second-level query condition where rownum <= 40 can be pushed to the inner-level query by oracle, so that once the oracle query results have checked the rownum restrictions,

Terminate the query and return the result. In the first statement, oracle cannot push the conditions on the third layer to the innermost layer (even if it is pushed to the innermost layer, it does not make sense because it does not know what RN represents ).

Case3: statement with order by (more efficient)

    SELECT T1.*, RN        FROM (SELECT ROWNUM RN, T.*                FROM (SELECT *                        FROM GAS_CA.A_CARD_METER_PAY A                       WHERE A.PURCH_GAS_TIME BETWEEN                             TO_DATE('2015/1/1', 'YYYY/MM/DD') AND                             TO_DATE('2016/1/1', 'YYYY/MM/DD')                         AND A.ORG_NO LIKE '1009%'                       ORDER BY A.PURCH_GAS_TIME) T               WHERE ROWNUM <= 20) T1       WHERE RN >= 10;  

Case4: placing query conditions on the outermost layer

    SELECT *        FROM (SELECT T.*, ROWNUM RN                FROM (SELECT *                        FROM GAS_CA.A_CARD_METER_PAY A                       WHERE A.PURCH_GAS_TIME BETWEEN                             TO_DATE('2015/1/1', 'YYYY/MM/DD') AND                             TO_DATE('2016/1/1', 'YYYY/MM/DD')                         AND A.ORG_NO LIKE '1009%'                       ORDER BY A.PURCH_GAS_TIME) T) T1       WHERE T1.RN BETWEEN 10 AND 20;