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语句，否则会影响性能。