網頁呈現分頁功能，利用SQL指令撈資料庫資料(oracle, MySQL)

MySQL資料庫的指令
使用LIMIT 選擇範圍大小
"SELECT * FROM product WHERE cate_id=? ORDER BY sale_time DESC LIMIT ?,?";

改成ORACLE寫法則為：

select * from (select  a.*,rownum rn from (select * from product order by sale_time desc) a where rownum <=3) where  rn>=1;

參考網站
https://blog.csdn.net/u010184335/article/details/11495975

案例一：

ROWNUM是一个伪列(不是真正的列，在表中并不真实存在）

是oracle数据库从数据文件或缓冲区中读取数据的顺序。请切勿理解成记录的行号

比如说你想查询第二行记录按下面的方法是查询不到的。

select * from test where rownum=2;(这是错误的）

ROWNUM如何工作？

由于表中的行没有行号，因此不能够查找表的第几行（大于1)记录

Rownum是在其通过查询的谓词部分之后并且在完成排序或聚集之前赋给行

rownum仅在当其分配后增1,故下面的查询永远不会有结果返回

select * from oss01.mms_head_his where rownum>1 (也是错误的）

以下结构的查询中语句执行顺序解释

select...,rownum

from t

where<where clause>

   group by<column>

having<having clause>

    order by<column>

• 实际执行顺序如下:
• 1)首先执行From和Where 子句(即查询部份)
• 2)从From/Where 中,Rownum分配给每个输出行并增1
• 3)使用Select
• 4)使用Group by
• 5)使用Having
• 6)数据被排序

select * from emp where rownum<=5 order by sal desc 
           开发人员的意图应该是想得到薪水排行前5位的人,但这条语句实现不了

需要使用：分页查询

Select * 
4)              From(Select a.*,Rownum rnum 
5)                            From (your_query_goes_here) a
6)                           Where Rownum <=:MAX_ROW_TO_FETCH)
7)            Where rnum>= :MIN:ROW_TO_FETCH;
8)           请避免使用下面的看起来等价的模式(?)：
9)           Select * 
10)               From (Select a.*,Rownum rnum 
11)                              From (your_query_goes_here) a)
12)           Where rnum between : MIN:ROW_TO_FETCH and <=:MAX

 

Rownum topN 的效率分析:

Rownum有Top-N优化功能，使用它可以避免大数据集合的负担沉重的排序操作。例如下面的语句(假定salary为未索引列)
         Select * from (select * from big_emp order by salary)  where ROWNUM<=N; 

1. 全扫描 big_emp表 (不可避免)
2. 使用一个数组：有N个元素 (大概此时是在内存中), 仅排序N行

这个就是oracle top 10处理方法

表tab_test;(cd varchar2(10) ,user_name varchar2(10) not null)
user_name 字段有索引，表中有1亿条记录，如何用一条sql取出user_name排倒序的后1000-1020条记录

create table tab_test (ID VARCHAR2(200)NOT NULL,createtime  DATE not null);

INSERT INTO tab_test(ID,createtime) SELECT ROWNUM,SYSDATE-ROWNUM/36000 FROM Dba_Objects;

BEGIN
    FOR i IN 1..6 LOOP
        INSERT INTO tab_test(ID,createtime)
        SELECT ROWNUM,SYSDATE+1-ROWNUM/10000 FROM tab_test;
        COMMIT;
     END LOOP;
END;

 

CREATE INDEX tab_test_id1 ON tab_test(createtime);

如何用一条sql取出user_name排倒序的后1000-1020条记录

select b.*
  from (select rownum rn, rid
          from (select rowid rid FROM tab_test order by CREATETIME DESC)
         where rownum <1020) a,
       tab_test b
 where rn >=1000
   and b.rowid = a.rid;（这是效率最高的方法）

另外一种方法：select * from (select  a.*,rownum rn from (select * from tab_test order by createtime desc) a where rownum <=1020) where  rn>=1000;

分析：

select  a.*,rownum rn from (select * from tab_test order by createtime desc) a where rownum <=1020

这一部分不需要全表扫描，有索引列

对于表的数据来讲你这个拿了1020行表的数据

表里面拿1020行数据，然后再过滤了1000行数据

最外面一层是只去表里拿了20行 1000行是在索引里面过滤掉。

案例2：

create table test as select * from dba_objects;

select * from test t where t.object_name='EMP';

建立视图：

create view v_test
 as
 select rownum as rid,
 t.object_id,
 t.object_name
 from test t;

set autot trace;

select * from v_test1 t where t.object_name='EMP';

和 （比较两个的执行计划）

select * from test1 t where t.object_name='EMP';

 

 

--------------------------------------------------------------------------------------------------- | Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |                     |     1 |   207 |     4   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| TEST                |     1 |   207 |     4   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | IX_TEST_OBJECT_NAME |     1 |       |     3   (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------ | Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT    |        | 60441 |  5430K|   287   (1)| 00:00:04 | |*  1 |  VIEW               | V_TEST | 60441 |  5430K|   287   (1)| 00:00:04 | |   2 |   COUNT             |        |       |       |            |          | |   3 |    TABLE ACCESS FULL| TEST   | 60441 |  4662K|   287   (1)| 00:00:04 |

可以看到，第二个没有使用索引 是因为他要先把ROWNUM算出来 再过滤

 

下一个经典应用：

 SELECT nvl(SUM(file_size), 0)
   FROM TCUSER.zc_user_file
  WHERE directory_id in
        (select directory_id
           from (select directory_id
                   from TCUSER.zc_directory
                  where user_number = '8613597061435'))
    AND EXPIRE_DATE >= SYSDATE
    and user_number = '8613597061435'

如果外面的表没有冗余那个字段,那么我们想要的执行计划就一定要保证子查询的结果先出来,而不是内推,因为如果是外面的先执行,则过滤条件只有EXPIRE_DATE >= SYSDATE
,而这个出来的记录会很多,这样会导致里面的语句的执行计划错误. 

这个结果集单独算出来

通过user_number的索引单独算出来

TCUSER.zc_directory，TCUSER.zc_user_file
直接关联

rownum 实体化子查询