mysql和orcal分页查询_oracle和mysql中的分页查询

转贴：

网站开发中少不了需要分页查询，就会有需要查询第3、4、5、6条记录的要求，以下从mysql和oracle两种数据库的使用。

Mysql的数据库很简单：select * from upp_extend_info limit 3,4

下面简单说说变态的oracle：

一、最简单的全部选择：

SQL> select * from upp_extend_info t;

ID                 LINEAGE

----------                 --------

100                 lineage

2                 lineage

13                 lineage

8                 lineage

17                 lineage

9                 lineage        11                 lineage

12                 lineage

16                 lineage

14                 lineage

15                 lineage

18                 lineage

7                 lineage

3                 lineage

4                 lineage

15 rows selected

二、使用rownum属性选择前6条记录，并且组成新的表T

SQL> select * from (select * from upp_extend_info t) T where rownum<=6;

ID                 LINEAGE

----------                 --------

100                 lineage

2                 lineage

13                 lineage

8                 lineage

17                 lineage

9                 lineage

6 rows selected

三、选择出前六条表以及rownum

SQL> select T.* ,rownum from (select * from upp_extend_info t) T where rownum<=6;

ID                 LINEAGE         ROWNUM----------                 ------------       ------

100                 lineage                 1

2                 lineage                 2

13                 lineage                 3

8                 lineage                 4

17                 lineage                 5

9                 lineage                 6

6 rows selected

四、给rownum重命名为num，防止与外层rownum冲突

SQL> select T.* ,rownum num from (select * from upp_extend_info t) T where rownum<=6;

ID                 LINEAGE            NUM----------                 ---------------      ---

100                 lineage                1

2                 lineage                2

13                 lineage                3

8                 lineage                4

17                 lineage                5

9                 lineage                6

6 rows selected

五、根据num重新选择rownum大于3的行

SQL> select * from (select T.* ,rownum num from (select * from upp_extend_info t) T where rownum<=6) where num>=3;

ID LINEAGE        NUM

---------- ------------------

13 lineage          3

8 lineage          4

17 lineage          5

9 lineage          6

总结：

mysql是提供默认的功能，指定 开始页码3 以及 行数4 来实现的分页

oracle不提供分页功能，需要用户自己实现，总的思路是先取出前6页组成新表，然后再计算出行数筛选出3-6条记录

终于感觉到mysql的傻瓜简单实在了吧！

================================***************=========================================

2009-8-11

今天看到一个分享，了解到DBA对oracle的分页方法进行优化，所以补充一个加强版！

假设activity_pepsi_dream表拥有 id, nick, auction_url, gmt_create 字段。

--普通写法(显示1-50页)

select * from (select T.* ,rownum num from (select * from upp_extend_info t) T where rownum<=6) where num>=3;

--rowid写法

select TTTT.*

from (select rid

from (select TT.rid,rownum num

from (select rowid rid

from upp_extend_info T)TT

where rownum<=6)

where num>=3)TTT,upp_extend_info TTTT

where TTT.rid=TTTT.rowid

性能比较：

---查询第一页普通写法要比rowid写法要快

---随着翻页次数的增多，普通写法需要回表的记录越来越多，性能下降很快。比如你要看200-250条的记录，这时候普通写法需要回表250条记录，而rowid写法只要会标200-250区间中的这50条记录

rowid 写法优化的情况：

--第一层必须都在索引中扫描，不回表

--第二三层找到满足条件的rowid

--最后根据rowid去回表找到记录

总结：关键就是oracle 的 rowid 属性可以最快速度的寻找到该行的数据，并且固定，不需要回表操作。

1、rowid是一个伪列，是用来确保表中行的唯一性，它并不能指示出行的物理位置，但可以用来定位行。 2、rowid是存储在索引中的一组既定的值(当行确定后)。我们可以像表中普通的列一样将它选出来。

3、利用rowid是访问表中一行的最快方式。

4、rowid需要10个字节来存储，显示为18位的字符串。

rowid的组成结构为：

data object number(6位字符串)+relative file number(3位字符串)+block number(6位字符串)+row number(3位字符串)，如：AAAADeAABAAAAZSAAA

5、我们可以借助oracle提供的包dbms_rowid，来对rowid进行解析从而获取关于行的相关信息：

bossdb-SQL>select

2 rowid,

3 dbms_rowid.rowid_object(rowid) obj_id,

4 dbms_rowid.rowid_relative_fno(rowid) df#,

5 dbms_rowid.rowid_block_number(rowid) blknum,

6 dbms_rowid.rowid_row_number(rowid) rowno

7 from p_test where rownum<5;

ROWID OBJ_ID DF# BLKNUM ROWNO

------------------ ---------- ---------- ---------- ----------

AAAQ+tAANAAAC6SAAA 69549 13 11922 0 AAAQ+tAANAAAC6SAAB 69549 13 11922 1 AAAQ+tAANAAAC6SAAC 69549 13 11922 2 AAAQ+tAANAAAC6SAAD 69549 13 11922 3

我们可以看到，通过rowid_row_number得到的行号是从0开始的，这是和rownum伪列的一个不同之处。我猜测rowid_row_number在求行号的时候是计算首行的偏移量。

一般来说，当表中的行确定后，rowid就不会发生变化。

但当如下情况发生时，rowid将发生改变：

1、对一个表做表空间的移动后

2、对一个表进行了EXP/IMP后