Oracle笔记——分页查询

分页查询

rownum : 逻辑行号

rowid ： 物理ID

在Web开发中，经常使用 page : 页码 ， pagesize ： 当前页有多少行 这两个参数来进行传参

rownum 不能使用大于号只能使用小于号或等号

效率低的分页 : 使用差集minus

效率高的分页 ：

---查询最大的行号 ： page * pagesize =3 *3 =9
select * from TBPROD where rowid in
(
  select rwd from
   ( select rwd, rownum  rn from
      (select rowid rwd from TBPROD order by pmark)
	---找出最大行号 page * pagesize
    where rownum <=6
   )
  ---找出起始行号：(page-1) * pagesize  
  where rn>3 
) order by pmark

细节：无法使用 Distinct,Group by 等子句从视图选择rowid

疑问： rowid 为行号的唯一标识，rownum是否代表其唯一标识

答案：不是,rownum在不同查询条件下有不同的值，根据结果集进行编排，但是rowid是固定唯一标识行号，而rownum根据结果集的数据来编排行号

典型例子：

 
 select  rowid,rownum, PID,PTYPE,PMARK,PSPEC from TBPROD;

/**显示数据
AAARXgAAEAAAAJHAAA	1	1	电视机	创维	48英寸等离子电视机
AAARXgAAEAAAAJHAAB	2	2	电视机	创维	56英寸数字电视机
AAARXgAAEAAAAJHAAC	3	3	电视机	创维	32英寸背投
AAARXgAAEAAAAJHAAD	4	4	电视机	熊猫	48英寸等离子电视机
AAARXgAAEAAAAJHAAE	5	5	电视机	熊猫	56英寸数字电视机
AAARXgAAEAAAAJHAAF	6	6	电视机	TCL	32英寸背投
AAARXgAAEAAAAJHAAG	7	7	笔记本	宏基	奔腾双核 T6666
AAARXgAAEAAAAJHAAH	8	8	笔记本	宏基	酷睿2双核 E7500 
AAARXgAAEAAAAJHAAI	9	9	笔记本	联想	奔腾双核 T6666
AAARXgAAEAAAAJHAAJ	10	10	笔记本	联想	酷睿2双核 E7500 
**/

 select  rowid,rownum, PID,PTYPE,PMARK,PSPEC from TBPROD  where ptype='笔记本'

/**显示数据
AAARXgAAEAAAAJHAAG	1	7	笔记本	宏基	奔腾双核 T6666
AAARXgAAEAAAAJHAAH	2	8	笔记本	宏基	酷睿2双核 E7500 
AAARXgAAEAAAAJHAAI	3	9	笔记本	联想	奔腾双核 T6666
AAARXgAAEAAAAJHAAJ	4	10	笔记本	联想	酷睿2双核 E7500 
**/

从上面例子可以看出，当查询条件变化时，结果集的相同记录的逻辑行号变化，但是物理ID却不变，始终指向相同一条记录，类似主键的作用，唯一标识一条记录。

疑问：order by 能否改变逻辑行号？

答案：order by 不改变原表的逻辑行号，改变order by 的值时，但当将排序过后的表作为嵌套查询时，此时逻辑行号就改变了。

典型例子：

 
---未排序之前
select rowid,rownum, PID,PTYPE,PMARK,PSPEC from SKYCOOP.TBPROD

/**显示数据
AAARXgAAEAAAAJHAAA	1	1	电视机	创维	48英寸等离子电视机
AAARXgAAEAAAAJHAAB	2	2	电视机	创维	56英寸数字电视机
AAARXgAAEAAAAJHAAC	3	3	电视机	创维	32英寸背投
AAARXgAAEAAAAJHAAD	4	4	电视机	熊猫	48英寸等离子电视机
AAARXgAAEAAAAJHAAE	5	5	电视机	熊猫	56英寸数字电视机
AAARXgAAEAAAAJHAAF	6	6	电视机	TCL	32英寸背投
AAARXgAAEAAAAJHAAG	7	7	笔记本	宏基	奔腾双核 T6666
AAARXgAAEAAAAJHAAH	8	8	笔记本	宏基	酷睿2双核 E7500 
AAARXgAAEAAAAJHAAI	9	9	笔记本	联想	奔腾双核 T6666
AAARXgAAEAAAAJHAAJ	10	10	笔记本	联想	酷睿2双核 E7500 
**/

---按pmark排序
select rowid,rownum, PID,PTYPE,PMARK,PSPEC from SKYCOOP.TBPROD order by pmark;

/**数据显示
AAARXgAAEAAAAJHAAF	6	6	电视机	TCL	32英寸背投
AAARXgAAEAAAAJHAAB	2	2	电视机	创维	56英寸数字电视机
AAARXgAAEAAAAJHAAC	3	3	电视机	创维	32英寸背投
AAARXgAAEAAAAJHAAA	1	1	电视机	创维	48英寸等离子电视机
AAARXgAAEAAAAJHAAG	7	7	笔记本	宏基	奔腾双核 T6666
AAARXgAAEAAAAJHAAH	8	8	笔记本	宏基	酷睿2双核 E7500 
AAARXgAAEAAAAJHAAI	9	9	笔记本	联想	奔腾双核 T6666
AAARXgAAEAAAAJHAAJ	10	10	笔记本	联想	酷睿2双核 E7500 
AAARXgAAEAAAAJHAAD	4	4	电视机	熊猫	48英寸等离子电视机
AAARXgAAEAAAAJHAAE	5	5	电视机	熊猫	56英寸数字电视机
**/

---按pspec排序
select rowid,rownum, PID,PTYPE,PMARK,PSPEC from SKYCOOP.TBPROD order by PSPEC;

/**
AAARXgAAEAAAAJHAAF	6	6	电视机	TCL	32英寸背投
AAARXgAAEAAAAJHAAC	3	3	电视机	创维	32英寸背投
AAARXgAAEAAAAJHAAA	1	1	电视机	创维	48英寸等离子电视机
AAARXgAAEAAAAJHAAD	4	4	电视机	熊猫	48英寸等离子电视机
AAARXgAAEAAAAJHAAB	2	2	电视机	创维	56英寸数字电视机
AAARXgAAEAAAAJHAAE	5	5	电视机	熊猫	56英寸数字电视机
AAARXgAAEAAAAJHAAG	7	7	笔记本	宏基	奔腾双核 T6666
AAARXgAAEAAAAJHAAI	9	9	笔记本	联想	奔腾双核 T6666
AAARXgAAEAAAAJHAAH	8	8	笔记本	宏基	酷睿2双核 E7500 
AAARXgAAEAAAAJHAAJ	10	10	笔记本	联想	酷睿2双核 E7500 
**/

---当排序后的表作为嵌套使用时，逻辑行号改变
select rowid, rownum,PID,PTYPE,PMARK,PSPEC from (select * from TBPROD order by pmark);

/**
AAARXgAAEAAAAJHAAF	1	6	电视机	TCL	32英寸背投
AAARXgAAEAAAAJHAAB	2	2	电视机	创维	56英寸数字电视机
AAARXgAAEAAAAJHAAC	3	3	电视机	创维	32英寸背投
AAARXgAAEAAAAJHAAA	4	1	电视机	创维	48英寸等离子电视机
AAARXgAAEAAAAJHAAG	5	7	笔记本	宏基	奔腾双核 T6666
AAARXgAAEAAAAJHAAH	6	8	笔记本	宏基	酷睿2双核 E7500 
AAARXgAAEAAAAJHAAI	7	9	笔记本	联想	奔腾双核 T6666
AAARXgAAEAAAAJHAAJ	8	10	笔记本	联想	酷睿2双核 E7500 
AAARXgAAEAAAAJHAAD	9	4	电视机	熊猫	48英寸等离子电视机
AAARXgAAEAAAAJHAAE	10	5	电视机	熊猫	56英寸数字电视机
**/

从上面的例子可以看出，改变order by 的值不能改变原表中的逻辑行号，但是在作为嵌套使用时，逻辑行号就会根据当前的顺序重新编排逻辑行号。值得注意的是，物理ID在整个过程中都未发生变化。

分析高效分页的基本思路：根据物理id来分页，为了能分页，就必须要明确范围，确定从哪里开始，从哪里结束就可以了。为此需要两个参数：page 页数， pagesize 每一页有多少。由这两个参数可以确定从哪里开始，从哪里结束。由于逻辑行号变化不定，且不能在条件判断中使用大于号，所以需要将逻辑行号自成一列。首先，获得物理id的指定排序下的结果集（J1），将此结果集作为嵌套表，添加逻辑行号，物理id为列并选出其哪里结束的位置的结果集（J2），在J2中选出哪里开始的位置作为结果集（ J3），此时结果集J3中已经有了某页的范围的物理id,最后只要将其作为嵌套表再查询就行了。