查询数据总量-where和having子句的使用以及使用内部函数提速

=====================

1、计算数据总量

有3种做法：

select count(1) from table_name;
select count(*) from table_name;
select count(字段) from table_name;

个人觉得count(索引过的字段)速度最快
count(*)其次
count(1)和count(未索引的字段)最慢

-----------------------

2、where子句与having子句

where子句可以限制以减少查询出来的数据量
而having子句则是在查询出来的数据基础上来进行过滤
所以最好使用where子句代替having子句

SQL> create table an(id int,name varchar2(50) default 'anbaisheng' not null);

表已创建。

SQL> begin
  2  for
  3  i in 1..10000 loop
  4  insert into an (id) values(i);
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL 过程已成功完成。

SQL> alter table an modify (name varchar2(50) default 'xiangxiang');

表已更改。

SQL> begin
  2  for
  3  i in 10001..20000 loop
  4  insert into an (id) values(i);
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL 过程已成功完成。

SQL> select count(*) from an;

  COUNT(*)
----------
     20000

SQL> set timing on
SQL> select count(*),name from an group by name having name='xiangxiang';

  COUNT(*) NAME
---------- --------------------------------------------------
     10000 xiangxiang

已用时间:  00: 00: 00.03
SQL> select count(*),name from an where name='xiangxiang' group by name;

  COUNT(*) NAME
---------- --------------------------------------------------
     10000 xiangxiang

已用时间:  00: 00: 00.01

效果还是很显著的

---------------------------------

通过内部函数提高sql效率

SELECT H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*)
FROM HISTORY_TYPE T,EMP E,EMP_HISTORY H
WHERE H.EMPNO = E.EMPNO
AND H.HIST_TYPE = T.HIST_TYPE
GROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;

通过调用下面的函数可以提高效率.

FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2
AS
TDESC VARCHAR2(30);
CURSOR C1 IS
SELECT TYPE_DESC
FROM HISTORY_TYPE
WHERE HIST_TYPE = TYP;
BEGIN
OPEN C1;
FETCH C1 INTO TDESC;
CLOSE C1;
RETURN (NVL(TDESC,'?'));
END;

FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2
AS
ENAME VARCHAR2(30);
CURSOR C1 IS
SELECT ENAME
FROM EMP
WHERE EMPNO=EMP;
BEGIN
OPEN C1;
FETCH C1 INTO ENAME;
CLOSE C1;
RETURN (NVL(ENAME,'?'));
END;

SELECT H.EMPNO,LOOKUP_EMP(H.EMPNO),
H.HIST_TYPE,LOOKUP_HIST_TYPE(H.HIST_TYPE),COUNT(*)
FROM EMP_HISTORY H
GROUP BY H.EMPNO , H.HIST_TYPE;

<汗~~~~~麻烦>