Oracle性能测试记录
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语句，否则会影响性能。
?
?
?
?
?
?
