Oracle分页查询与解决方案

Question

1.ROWNUM:
概念：OORACLE使用ROWNUM是一个伪列，数据库提取记录才会生成的数值1,2,3，4 作用是用于实现ORACLE的分页必须使用子查 询实现执行流程(带条件) :  a.查询EMPLOYEES表，生成EMOLYEES伪列
                              b.根据分页条件判断该ROWNUM是否与该条件匹配
                              c.条件匹配，取出该条条件
                              d.生成第二个ROWNUM重复r操作
 2.别名问题
由于ROWNUM的WHERE判断执行在SELECT关键字之前。当前查询中的ROWNUM别名不能用于条件做判断，别名只可以用一外部条件判断3.ROWID是数据库保存记录时候生成的真实物理地址，唯一不变
作用：数据库操作记录使用
         索引值→ROWID→将ROWID换成算成一行数据的物理地址→得到一行数据
如：提取员工表的前三行数据
如：提取4行之后的数据
            错误例子：SELECT ROWNUM，EMP.* FROM EMP WHERE ROWNUM > 3 --错误
            生成第一个ROWNUM，进行条件判断是不符合，无法提取结果，结果为NULL
解决方案：先查询带ROWNUM的伪表SELECT ROWNUM，EMP.* FROM EMP查询伪表，选出4条以后的数据SELECT * FROM(SELECT ROWNUM R,EMP.* FROM EMP) RE WHERE RE.R > 3
1.提取工资排行前三的员工虽然查询了结果，但是似乎并不是前工资排名前三的员工，
原因分析：WHERE条件的执行在ORACLE BY 之前，页就是先生成了ROWNUM之后才进行，显然者时候序号已经生成好
正确的是SELECT * FROM EMP ORACLE BY SALARY DESC先排序，SELECT ROWNUM,E.* FROM (SELECT * FROM EMP ORACLE BY SAL DESC) E WHERE ROWNUM BETWEEN 1 AND 42.提取6到10的记录数据效率我要提取的数据是6到10之前的数据，把不需要用到的数据过滤掉，提高效率后的写法，只进行提取分页(表数据多是效率会极低)。
如图下。

Answer 1

问个问题 下面这个语句怎么优化到秒出（假设：emp表上千万，满足deptno条件的过五百万）：
SELECT * FROM (SELECT rownum rn, emp.* FROM EMP WHERE DEPTNO='DEVELOPER' ORACLE BY SAL DESC )   WHERE ROWNUM <= 20

Answer 2

那个，拷贝的时候没注意，where条件后的ORACLE BY应该是ORDER BY……

Answer 3

加了 deptno, sal 的联合索引后，应该很快能出结果。 

Answer 4

加了 deptno, sal 的联合索引后，应该很快能出结果。 其实不一定 

Answer 5

加了 deptno, sal 的联合索引后，应该很快能出结果。 其实不一定 举个例子，最好贴一下你的执行过程。

Answer 6

加了 deptno, sal 的联合索引后，应该很快能出结果。 其实不一定 举个例子，最好贴一下你的执行过程。例子可以举一个，当然比较极端，如果表里有数据同时deptno,sal为空，那么这条索引没法用到这种分页SQL中，当然从业务上来讲，这个不可能，但是如果这两个字段上都没有not null约束，或者主键约束，那么即使业务上数据不可能全为空，oracle还是不会选择这条索引。

Answer 7

-- 这个实验结果，你还满意不Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as [email protected]/testSQL> 
SQL> set timing on;
SQL> drop index ix_emp;
drop index ix_emp
ORA-01418: 指定的索引不存在
SQL> select count(*) from emp;
  COUNT(*)
----------
  14221312
Executed in 2.886 seconds
SQL> select count(*) from emp where deptno is null;
  COUNT(*)
----------
    819200
Executed in 2.366 seconds
SQL> select count(*) from emp where sal is null;
  COUNT(*)
----------
   6094848
Executed in 2.861 seconds
SQL> select count(*) from emp where deptno = 'DEV';
  COUNT(*)
----------
   7700480
Executed in 2.548 seconds
SQL> select count(*) from ind where table_name = 'EMP';
  COUNT(*)
----------
         0
Executed in 0.037 seconds
SQL> explain plan for SELECT * FROM
  2  (SELECT rownum rn, emp.* FROM EMP WHERE DEPTNO='DEV' ORDER BY SAL DESC )
  3    WHERE ROWNUM <= 20;
Explained
Executed in 0.015 seconds
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1035293170
--------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |    20 | 10740 |       |   841K  (1)| 02
|*  1 |  COUNT STOPKEY          |      |       |       |       |            |
|   2 |   VIEW                  |      |  6401K|  3278M|       |   841K  (1)| 02
|*  3 |    SORT ORDER BY STOPKEY|      |  6401K|  3198M|  4167M|   841K  (1)| 02
|   4 |     COUNT               |      |       |       |       |            |
|*  5 |      TABLE ACCESS FULL  | EMP  |  6401K|  3198M|       |   132K  (1)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=20)
   3 - filter(ROWNUM<=20)
   5 - filter("DEPTNO"='DEV')
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
23 rows selected
Executed in 0.213 seconds
SQL> rollback;
Rollback complete
Executed in 0.002 seconds
SQL> SELECT * FROM
  2  (SELECT rownum rn, emp.* FROM EMP WHERE DEPTNO='DEV' ORDER BY SAL DESC )
  3    WHERE ROWNUM <= 20;  -- 这里的数据我删除了
20 rows selected
Executed in 7.728 seconds
SQL> create index ix_emp on emp(deptno, sal);
Index created
Executed in 16.774 seconds
SQL> exec dbms_stats.gather_table_stats(user,'EMP',cascade => true);
PL/SQL procedure successfully completed
Executed in 16.812 seconds
SQL> explain plan for SELECT * FROM
  2  (SELECT rownum rn, emp.* FROM EMP WHERE DEPTNO='DEV' ORDER BY SAL DESC )
  3    WHERE ROWNUM <= 20;
Explained
Executed in 0.006 seconds
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2189114292
--------------------------------------------------------------------------------
| Id  | Operation                       | Name   | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |        |    20 | 10740 |    18   (0)|
|*  1 |  COUNT STOPKEY                  |        |       |       |            |
|   2 |   VIEW                          |        |    20 | 10740 |    18   (0)|
|   3 |    COUNT                        |        |       |       |            |
|   4 |     TABLE ACCESS BY INDEX ROWID | EMP    |  7714K|  1611M|    18   (0)|
|*  5 |      INDEX RANGE SCAN DESCENDING| IX_EMP |    20 |       |     3   (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=20)
   5 - access("DEPTNO"='DEV')
18 rows selected
Executed in 0.194 seconds
SQL> rollback;
Rollback complete
Executed in 0.002 seconds
SQL> SELECT * FROM
  2  (SELECT rownum rn, emp.* FROM EMP WHERE DEPTNO='DEV' ORDER BY SAL DESC )
  3    WHERE ROWNUM <= 20;
  -- 这里的数据我删除了
20 rows selected
Executed in 0.364 secondsSQL> 

Answer 8

看上一楼是我想茬了，因为已经有了where deptno='DEV'这个条件…… 

Answer 9

关于分页查询，有一个必须注意的问题，就是先排序，再取行，否则会有问题。
另外，分页中用到的大于号小于号，有点过时了，应该用BETWEEN。

Answer 10

用between代码倒是好看了，性能完了。
排序也是，排序了就需要额外的优化手段。如果你说的问题是翻页后数据重复的问题，那么排序还需要加在唯一性很好的字段上。

Answer 11

这两种写法，几乎是在同一时间出现的。从性能上来讲，这两种分页查询的差异表现如下：
between 的写法，查询每页的时间是一样的。
先 小于，再大于的写法，第一页的速度是最快的，第二页要慢一些，第三页再慢一些，最后一页的性能和 between是一样的。

Answer 12

我看视频测试结果 用 rowid 时 最快

Answer 13

select *
  from emp
 where rowid in (select rid
                   from (select rownum rn, rid
                           from (select rowid rid from emp)
                          where rownum < 5)
                  where rn > = 2)