Oracle个人笔记（SQL性能）2A

KelivenLin

1.
 ...group by (code,name)； 性能要比 ...group by code,name ；强多
 
2.
在Oracle中，考虑惟一性时（NULL,NULL）与（NULL,NULL）并不相同，这是SQL标准要求的。
不过对于聚集来说（NULL,NULL）和（NULL,NULL）则认为是相同的。
两个（NULL,NULL）在比较时并不相同，但是对GROUP BY 子句来说却是一样的.
所以应当考虑到：每个惟一约束应该至少有一个确实惟一的NOT NULL列。
 
3.性能最高的删除重复记录的SQL
delete from AJ_SAWP r
where r.rowid in
 (select c.rowid from AJ_SAWP c,AJ_SAWP t where c.sjgxsj<t.sjgxsj and c.sjgxsj is not null
  and c.WPBH=t.WPBH);
 
4.执行更新SQL更新数据记录 并不会改变该记录的rowid
5.用>=替代>,效率提高。
6.IN和EXISTS 比较
SELECT COUNT(*) FROM CZRK WHERE GMSFHM IN (
       SELECT GMSFHM FROM CZRK HAVING COUNT(GMSFHM) > 1 GROUP BY GMSFHM
       );--137786行， 154.016 秒

SELECT COUNT(*) FROM CZRK T where EXISTS (
       SELECT GMSFHM FROM CZRK R WHERE T.GMSFHM=R.GMSFHM HAVING COUNT(GMSFHM) > 1 GROUP BY GMSFHM
       ); -- 137786行， 364.203--秒
    
      
7.用外联接提高表连接的查询速度
　　在作表连接(常用于视图)时，常使用以下方法来查询数据:
  SELECT PAY_NO, PROJECT_NAME
　　FROM A
　　WHERE A.PAY_NO NOT IN (SELECT PAY_
　　NO FROM B WHERE VALUE >=120000);
  　----但是若表A有10000条记录，表B有10000条记录，则要用掉30分钟才能查完，
  主要因为NOT IN要进行一条一条的比较，共需要10000*10000次比较后，才能得到结果。
  该用外联接后，可以缩短到1分左右的时间:
　　SELECT PAY_NO,PROJECT_NAME
　　FROM A,B
　　WHERE A.PAY_NO=B.PAY_NO(+)
　　AND B.PAY_NO IS NULL
　　AND B.VALUE < 12000;
8.用EXISTS替代IN、用NOT EXISTS替代NOT IN：
 （高效）SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)
　　(低效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’)
9. 用EXISTS替换DISTINCT：
  (低效):
　　SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E
　　WHERE D.DEPT_NO = E.DEPT_NO
　　(高效):
　　SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X’
　　FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
10.避免在索引列上使用计算
  WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．
　　举例:
　　低效：
　　SELECT … FROM DEPT WHERE SAL * 12 > 25000;
　　高效:
　　SELECT … FROM DEPT WHERE SAL > 25000/12;
11.用>=替代>
12.巧用 case when...then...else 嵌套简化SQL
 select p.rq,SUM(case when p.shengfu='胜' then 1 else 0 end) WIN,
 SUM(case when p.shengfu='负' then 1 else 0 end) LOST
 from tmp p
 group by p.rq;----------------简洁
 
 select distinct p1.rq, t.s_times,r.f_times    
  from tmp p1,(select p2.rq,p2.shengfu,count(*) s_times from tmp p2 where p2.shengfu='胜' group by(rq,shengfu)) t,
  (select p3.rq,p3.shengfu,count(*) f_times from tmp p3 where p3.shengfu='负' group by (rq,shengfu)) r
  where p1.rq=t.rq and t.rq=r.rq;---------繁琐
13.强制索引失效
     A.如果两个或以上索引具有相同的等级，则可以强制命令ORACLE优化器使用其中一个（通过它检索出记录数量少）。
     B.      SELECT ENAME FROM EMP
  WHERE ENPNO = 793
  AND DEPTNO + 0 = 10  /*DEPTNO上索引将失效*/
  AND EMPTYPE || '' = 'A';  /*EMPTYPE上索引将失效*/