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 AS i 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_ID minvalue 1 maxvalue 999999999999999999999999999 start with 3000001 increment by 1 nocache;

 

表的触发器

create or replace trigger TRG_USERS_ID before insert on users for each row begin 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秒， 性能没有大的波动