ORACLE通用查询性能优化

声明：测试环境

(1)windows xp，2G内存，2.66G 单核CPU

(2)manager用户有单独的表空间与临时表空间

(3)300万条数据

表结构如下

create table USERS ( ID NUMBER(32) not null, NAME VARCHAR2(50) not null, AGE NUMBER(4) not null, MONEY NUMBER(10,2) not null, CLASS_ID NUMBER(32) not null, RAND_CODE NUMBER(32) not null, V_DATE_TIME VARCHAR2(19) not null, D_DATE_TIME DATE not null, constraint PK_USERS primary key (ID));

 

用来造数据的存储过程，如下：

CREATE OR REPLACE PROCEDURE pr_test ASi NUMBER(32) DEFAULT 1;r_rand NUMBER(32) DEFAULT 1;sn_rand NUMBER(2) DEFAULT 1;n_rand NUMBER(2) DEFAULT 1;n_rand2 NUMBER(2) DEFAULT 1;c_rand NUMBER(2) DEFAULT 1;a_rand NUMBER(3) DEFAULT 0;m_rand NUMBER(10,2) DEFAULT 0.00;--姓氏数组type surname_array is varray(10) of varchar2(10);sn_array surname_array := surname_array('刘','赵','钱','孙','李','周','吴','郑','王','黄');--名字数组type name_array is varray(20) of varchar2(20);n_array name_array := name_array('工','用','国','中','怡','心','大','礼','光','淡','肖','田','伟','立','华','少','定','成','龙','秀');--班级数组type class_array is varray(10) of number(32);c_array class_array := class_array(1011,1012,1013,1014,1015,1016,1017,1018,1019,1010);BEGIN LOOP --选择[1,10]的随机数 select trunc(dbms_random.value(1,11)) INTO sn_rand from dual; --选择[1,20]的随机数 select trunc(dbms_random.value(1,21)) INTO n_rand from dual; select trunc(dbms_random.value(1,21)) INTO n_rand2 from dual; --选择[1,10]的随机数 select trunc(dbms_random.value(1,11)) INTO c_rand from dual; --选择[0,130]的随机数 select trunc(dbms_random.value(0,131)) INTO a_rand from dual; --选择[0,10000000]的随机小数 select abs(mod(dbms_random.random,1000000000))/100 into m_rand from dual; select trunc(dbms_random.value(1,999999999)) into r_rand from dual; INSERT INTO USERS (name, age, money, class_id, rand_code, v_date_time, d_date_time) VALUES ( sn_array(sn_rand) || n_array(n_rand) || n_array(n_rand2), a_rand, m_rand, c_array(c_rand), r_rand, to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'), sysdate ); i := i + 1; EXIT WHEN i > 6000000; END LOOP; commit;END;

 

表的主键序列

-- Create sequence create sequence SQ_USERS_IDminvalue 1maxvalue 999999999999999999999999999start with 3000001increment by 1nocache;

 

表的触发器

create or replace trigger TRG_USERS_ID before insert on users for each rowbegin select sq_users_id.nextval into :new.id from dual;end TRG_USERS_ID;

 

一：最常用的ORACLE分页(单表)

(1)

select * from(
  select rownum rn, a1.* from(
    select u.id, u.name, u.age, u.money, u.class_id, u.rand_code, u.v_date_time, u.d_date_time
    from users u
  )a1 where rownum <= 10
)where rn >=5

 

(2)

select * from(
  select rownum rn, a1.* from(
    select u.id, u.name, u.age, u.money, u.class_id, u.rand_code, u.v_date_time, u.d_date_time
    from users u
  )a1
)where rn between 5 and 10

 

结论：(1)的平均时间在0.03xx秒，(2)的平均时间在1.25xx秒， 125/3， 效率大概相差40倍

------1：将(1)和(2)的from user u 改为 from user u order by u.id desc (注：id为PK)，

(1)查询时间平均为0.04x秒，(2)的平均查询时间为3.4xx秒，340/4，效率大概相差快100倍

------2：将(1)和(2)的from user u 改为 from user u order by u.age desc (注：age无索引)，

(1)查询时间平均为 1.9xx秒，(2)的平均查询时间为30.xxx秒，300/2，效率大概相差快150倍

------3：将(1)和(2)的from user u 改为 from user u order by u.age desc, u.name desc(注：age无索引, name无索引)，

(1)查询时间平均为 1.9xx秒，(2)的平均查询时间为32.xxx秒，320/2，效率大概相差快160倍

------4：在3的基础上只加上一个inner join，

(1)查询时间平均为7.1 xx秒，(2)的平均查询时间为33.xxx秒，330/7，效率大概相差快 50倍

二：关于where 1 = 1(单表)

select u.*
from users u
where u.rand_code in (37908470,342553519,417764204)
and 1 = 1

加不加 and 1 = 1 这个条件，查询时间都在0.9xx秒，性能没有大的波动

 

三：关于 select u.* 和 select u.i, u.name, u.age (单表)

测试用SQL语句同二， 查询时间也在0.9xx秒，性能没有大的波动

 

四：where u.age = 20 与 where u.age = '20'(单表)

测试用SQL语句同二， 查询时间也在0.9xx秒，性能没有大的波动

 

五：关于select count(*), count(1) 与 count(u.age)(单表)

测试用SQL语句同二， 查询时间也在0.5xx秒，性能没有大的波动