最近在看梁敬彬老师关于Oracle性能优化的一些案例，在这里做一些简单的总结

1.COUNT(*)与COUNT(列)哪个更快

drop table t purge;

create table t as select * from dba_objects;

--alter table T modify object_id  null;

update t set object_id =rownum ;

set timing on 

set linesize 1000

set autotrace on 



select count(*) from t;

/

select count(object_id) from t;

推论1：在不建索引的情况下，count（*）和count(列)没有区别

看来count(列)比count(*) 更快是谣传，明明是一样快嘛，真相是这样吗?



建索引试试

create index idx_object_id on t(object_id);

select count(*) from t;



select count(object_id) from t;



推论2：原来真的是用COUNT(列）比COUNT(*)要快啊，因为COUNT(*)不能用到索引，而COUNT(列)可以，真相真是如此吗？



继续



alter table T modify object_id  not  null;

select count(*) from t;

select count(object_id) from t;

推论3：看来count(列)和count(*)其实一样快，如果索引列是非空的，count(*)可用到索引，此时一样快！真相真是如此吗？

其实两者根本没有可比性，性能比较首先要考虑写法等价，这两个语句根本就不等价！

最佳字段顺序（结论：越往后的列访问CPU开销大）



验证脚本1 （先构造出表和数据）

SET SERVEROUTPUT ON

SET ECHO ON

---构造出有25个字段的表T

DROP TABLE t;

DECLARE

  l_sql VARCHAR2(32767);

BEGIN

  l_sql := 'CREATE TABLE t (';

  FOR i IN 1..25 

  LOOP

    l_sql := l_sql || 'n' || i || ' NUMBER,';

  END LOOP;

  l_sql := l_sql || 'pad VARCHAR2(1000)) PCTFREE 10';

  EXECUTE IMMEDIATE l_sql;

END;

/

----将记录还有这个表T中填充

DECLARE

  l_sql VARCHAR2(32767);

BEGIN

  l_sql := 'INSERT INTO t SELECT ';

  FOR i IN 1..25

  LOOP

    l_sql := l_sql || '0,';

  END LOOP;

  l_sql := l_sql || 'NULL FROM dual CONNECT BY level <= 10000';

  EXECUTE IMMEDIATE l_sql;

  COMMIT;

END;

/



--验证脚本2（一次访问该表各字段验证） 

execute dbms_stats.gather_table_stats(ownname=>user, tabname=>'t')

SELECT num_rows, blocks FROM user_tables WHERE table_name = 'T';

--以下动作观察执行速度，比较发现COUNT(*)最快，COUNT(最大列）最慢

DECLARE

  l_dummy PLS_INTEGER;

  l_start PLS_INTEGER;

  l_stop PLS_INTEGER;

  l_sql VARCHAR2(100);

BEGIN

  l_start := dbms_utility.get_time;

  FOR j IN 1..1000

  LOOP

    EXECUTE IMMEDIATE 'SELECT count(*) FROM t' INTO l_dummy;

  END LOOP;

  l_stop := dbms_utility.get_time;

  dbms_output.put_line((l_stop-l_start)/100);





  FOR i IN 1..25

  LOOP

    l_sql := 'SELECT count(n' || i || ') FROM t';

    l_start := dbms_utility.get_time;

    FOR j IN 1..1000

    LOOP

      EXECUTE IMMEDIATE l_sql INTO l_dummy;

    END LOOP;

    l_stop := dbms_utility.get_time;

    dbms_output.put_line((l_stop-l_start)/100);

  END LOOP;

END;

/



结论：

原来优化器是这么搞的：列的偏移量决定性能，列越靠后，访问的开销越大。由于count(*)的算法与列偏移量无关，所以count(*)最快。

后面还有看图说话，看看结果输出的趋势图，就更了然了。

2.IN和EXISTS之争

10g执行一下

select * from v$version;

drop table emp purge;

drop table dept purge;

create table emp as select * from scott.emp;

create table dept as select * from scott.dept;

set timing on 

set linesize 1000

set autotrace traceonly 

select * from dept where deptno NOT IN ( select deptno from emp ) ;

select * from dept where not exists ( select deptno from emp where emp.deptno=dept.deptno) ;



select * from dept where deptno NOT IN ( select deptno from emp where deptno is not null) and deptno is not null;

-结论：10g与空值有关，如果确保非空，可以用到anti的半连接算法

11g执行一下

select * from v$version;



drop table emp purge;

drop table dept purge;

create table emp as select * from scott.emp;

create table dept as select * from scott.dept;

set timing on 

set linesize 1000

set autotrace traceonly explain

select * from dept where deptno NOT IN ( select deptno from emp ) ;

select * from dept where not exists ( select deptno from emp where emp.deptno=dept.deptno) ;



select * from dept where deptno NOT IN ( select deptno from emp where deptno is not null) and deptno is not null;



结论：11g与空值有关，都可以用到anti的半连接算法，执行计划一样，性能一样