Oracle 11g,，IBM pc机，winxp，双核3GHz，3G内存

?

1.查看表结构

?

SQL> desc test;
Name??????????? Type?????????? Nullable Default Comments
--------------- -------------- -------- ------- --------
ID????????????? NUMBER(8)???????????????????????????????
SER_CODE??????? VARCHAR2(128)???????????????????????????
SOAP_ACTION???? VARCHAR2(1024)??????????????????????????
TARGET_SER_CODE VARCHAR2(100)???????????????????????????
TARGET_SER_OPER VARCHAR2(256)???????????????????????????
REPLY_SER_CODE? VARCHAR2(100)? Y????????????????????????
REPLY_SER_OPER? VARCHAR2(1024) Y?

?

2.获取总记录数（耗时3.297s）

?

SQL> select count(*) from test;
?
? COUNT(*)
----------
??? 638976

?

3.拷贝10W记录并创建新表

?

SQL> create table test2 as select * from test where rownum < 100000;

耗时3.469s

?

4.查询1000条记录

SQL> select * from test where rownum < 1000;

耗时0.966s

?

5.选取rownum在2-8之间的记录

?

SQL> select * from (select rownum rn , id from test) t where t.rn between 2 and 8;
?
??????? RN??????? ID
---------- ---------
???????? 2?????? 302
???????? 3???????? 1
???????? 4???????? 2
???????? 5???? 30303
???????? 6???? 30304
???????? 7?????? 301
???????? 8?????? 302

耗时3.515s

?

SQL> select * from (select rownum rn,id from test where rownum < 8) t where t.rn between 2 and 8;

??????? RN??????? ID
---------- ---------
???????? 2?????? 302
???????? 3???????? 1
???????? 4???????? 2
???????? 5???? 30303
???????? 6???? 30304
???????? 7?????? 301
???????? 8?????? 302

耗时0.056s

?

6.综合测试

select * from (select rownum rn , id from test where rownum <10000) t where t.rn between 2 and 10000;

耗时4.812s

?

select * from (select rownum rn , id from test) t where t.rn between 2 and 10000;

耗时8.172s

?

select * from (select rownum rn , id from test where rownum <100000) t where t.rn between 2 and 100000;

耗时47.453

select * from (select rownum rn , id from test) t where t.rn between 2 and 100000;

耗时50.703s

?

结论：如果使用rownum选取某一范围记录，使用子查询方式时应该加上 where rownum语句，否则会影响性能。

?

?

?

?

?

?