Rownum,rowid,row_number () and Oracle paging queries.

Both 1.rownum and rowid are pseudo-columns, but the root of the two is different, and RowNum is based on the results of the SQL query, assigning each row a

The logical number, different SQL will also cause rownum different, but ROWID is physical structure, when each record insert to the database,

There will be a unique physical record. rowID can be said to exist in the house, indicating a unique location ID recorded in the table space, in DB

The only. As long as the record has not been moved, ROWID is unchanged. rowID is the equivalent of a normal column relative to a table.

RowNum often used for paging query, ROWID is often used to remove duplicate records.

2.rownum is a pseudo-column added to the result set, which is the result set followed by a column (the result set first ). Simply put, rownum

is the serial number that matches the result of the condition . Always starting from 1, so the chosen result must not be 1 and other values greater than 1.

It can also be understood that rownum is the order in which the Oracle database reads data from a data file or buffer. It gets the first record, then the rownum is 1,

The 2nd article is 2. If you use >,>=,=,between...and these conditions, because the rownum of the first record obtained from the buffer or data file

is 1, then is deleted, then remove the bar, but RowNum is still 1, has been deleted, there is 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 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   &NB Sp  clerk      7902 1980/12/17     800.00               20&N bsp;7499 ALLEN      salesman   7698 1981/2/20     1600.00    300.00     30 7521 WARD       salesman   7698 1981/2/22     1250.00    500.00   &NB Sp  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      man Ager    7839 1981/6/9      2450.00               10 7788 SCOTT      analyst    7566 1987/4/19     3000.00           &NB Sp    20 7839 KING       President       1981/11/17    5000.00               10 7844 TURNER     salesman   7698 1981/9/8      15 00.00      0.00     30 7876 ADAMS      clerk      7788 1987/5/     1100.00               20 7900 JAMES      clerk &NB Sp    7698 1981/12/3      950.00               30 7902 FORD     &NBsp ANALYST    7566 1981/12/3     3000.00               20 7934 MI Ller     Clerk      7782 1982/1/23     1300.00           &NBSP ;   1014 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 7499               ALLEN      salesman   7698 1981/2/20     1600.00    300.00     7521 WARD       salesman 7698 1981/2/22 1250.00    500.00     30

3.rownum and Row_numbe () over:

When sorting using rownum , the result set is first added to the pseudo-column rownum and then sorted, and Row_number () is included

Sort the subordinate clauses and then calculate the line number first.

Case

ORDER BY:

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 7499 ALLEN salesman 7698 1981/2/20 1600.00 300.00 2 7698 BLAKE MANAGER 7839 1981/          5/1 2850.00 6 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 7 7902 FORD ANALYST 7566 1981/12/3 3000.00 7900 JAMES Clerk 7698 198          1/12/3 950.00 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 4 7839 KING President 1981/11/17 5000.00 9 7654 MARTIN salesman 7698 1  981/9/28 1250.00 1400.00 5 7934 MILLER Clerk 7782 1982/1/23   1300.00 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 8 7      369 SMITH Clerk 7902 1980/12/17 800.00 1 7844 TURNER salesman 7698 1981/9/8 1500.00 0.00 7521 WARD salesman 7698 1981/2/22 1250.00 500.00 30 3 Rows selected

Row_number () over (order by)

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 1 7499 ALLEN salesman 7698 198               1/2/20 1600.00 300.00 2 7698 BLAKE MANAGER 7839 1981/5/1 2850.00                              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 6 7566 JONES      MANAGER 7839 1981/4/2 2975.00 7 7839 KING President 1981/11/17 5000.00 8 7654 MARTIN salesman 7698 198               1/9/28 1250.00 1400.00 9 7934 MILLER clerk 7782 1982/1/23 1300.00                             Ten 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20                             7369 SMITH Clerk 7902 1980/12/17 800.00 20       7844 TURNER salesman 7698 1981/9/8 1500.00 0.00 (7521 WARD) Salesman 7698 1981/2/22 1250.00 500.00-1414 rows selected

4.

CASE1: Control 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 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 <=) WHERE RN >= 10;

For Case1 and Case2, in most cases, the CASE2 is much more efficient than the first query.

Because in the CBO optimization mode, Oracle can push the outer query condition into the inner query to improve the efficiency of the inner query execution. For the second statement,

The second-level query condition where rownum<=40 can be pushed into an inner query by Oracle so that the results of an Oracle query are checked against the conditions of the rownum limit,

Returns the result on the terminating query. In the first statement, Oracle cannot push the third-level condition to the inner layer (even pushing to the inner layer is meaningless, because it does not know what RN represents)

。

CASE3: With order by (higher efficiency)

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 <=) T1 where RN & gt;= 10;

CASE4: Placing query criteria at 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 and 20;

Rownum,rowid,row_number () and Oracle paging queries.