Oracle performance test records

Oracle 11g, ibm pc, winxp, dual-core 3 GHz, 3g memory

1. view the table structure

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_1_varchar2 (256)
REPLY_SER_CODE VARCHAR2 (100) Y
REPLY_SER_OPER VARCHAR2 (1024) Y

2. Get the total number of records (3.297 s)

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

3. Copy 10 million records and create a new table

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

3.469 s elapsed

4. query 1000 records

SQL> select * from test where rownum <1000;

0.966 s elapsed

5. Select a record with rownum between 2 and 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.515 s elapsed

 

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.056 s elapsed

6. Comprehensive Test

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

4.812 s elapsed

 

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

8.172 s elapsed

 

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

Time consumed: 47.453

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

50.703 s elapsed

Conclusion: If rownum is used to select a range of records, the where rownum statement should be added in the subquery mode. Otherwise, the performance will be affected.