Oracle数据库优化与常见得问题

标签: 数据优化
52人阅读 评论(0) 收藏 举报
分类:

数据库优化与问题


序言,本人做了将近的一年报表的开发,大多时间写sql,我想把自己学到关于数据库知识分享给大家,这里主要说一下常用的数据库优化

  1. 什么是数据库优化
    我的认识是,就是操作数据花的时间最少,准确性更高。这里讲一下常用sql优化技巧与生活常见的问题

  2. 一般遇到去重复的数据,一般会用到distinct函数,可以用group by 代替,注意一点时distinct不能和count(1)连用
    例子
    SELECT DISTINCT(mgr) FROM emp
    –用group by 代替distinct函数
    SELECT mgr FROM emp GROUP BY mgr
    –正确的写法
    SELECT COUNT(1),mgr FROM emp GROUP BY mgr
    –错误的写法
    SELECT COUNT(1),DISTINCT(mgr)FROM emp

  3. 在用group by 时,不一把所有的要查询的列都要放在group by 后面,有时候可以在max(列名)
    例子
    –两个查询结果一样,第一个效率更好
    SELECT ename,max(job),max(mgr) FROM emp GROUP BY ename

    SELECT ename,job,mgr FROM emp GROUP BY ename,job,mgr

  4. 在比较大小时注意
    例子
    SELECT * FROM EMP WHERE MGR>3
    优化后
    select * from emp where mgr>=4

  5. 在做查询,尽量不要进行全表查询
    例子
    –全表查询
    SELECT * FROM EMP
    –针对某一列
    SELECT mgr FROM EMP

  6. count(1)与count(*)区别
    count(1)是针对第一列进行统计,不统计第一列为空的现象
    但count(*)是统计为空现象

  7. sum()与count()的区别
    sum是某一列的和,count是计数的
    sum(0)就是0

  8. 求百分比出现的问题
    例子
    会出现小数点前不显示0,最好的解决办法
    SELECT to_char(round(800/1000,2)*100,’999’)||’%’ FROM dual

  9. 一般count(1)结果没有数据时会显示0,一般后面有条件的话,就不会显示零

  10. 尽量减少大表之间关联,特别是外连接,改用嵌套SELECT
    例如
    SELECT a.deptno,b.deptno FROM emp a,dept b WHERE a.deptno=b.deptno
    –性能优
    SELECT (SELECT b.deptno FROM dept b WHERE a.a.deptno=b.deptno) a,a.deptno FROM emp a

  11. 有索引,但全表扫表时,需要注意传入的参数,不一定是索引不好用
    对字段使用函数后进行比较,无法利用索引。

  12. in 可以改成exists函数
    例子
    SELECT * FROM emp a,dept b
    WHERE a.deptno=b.deptno
    –优化
    SELECT * FROM emp a WHERE Exists(SELECT 1 FROM dept WHERE a.deptno=b.deptno)

    SELECT * FROM emp a WHERE a.deptno in(select b.deptno from dept d where a.deptno=b.deptno)
    –优化
    SELECT * FROM emp a WHERE exists(select b.deptno from dept d where a.deptno=b.deptno)

  13. count()与sum()
    例子
    select count(*)from emp where mgr>100
    select count(*) from emp where mgr>200
    优化
    select count(case when mgr>100 then 1 else null end),
    count(case when mgr>200 then 1 else null end),from emp

  14. sql语句的执行顺序是自下而上,从右到左,所以写select语句时,要把限制最大的条件写在最下面

  15. sql语句用大写的;因为oracle总是先解析sql语句,把小写的字母转换成大写的再执行

  16. 避免在索引列上使用NOT 通常, 
    我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.

  17. union all 代替 union,因为union all有自动排序功能

  18. 用in 代替or

  19. 避免在索引列上使用IS NULL和IS NOT NULL

  20. 多个时间范围,查询结果为去除重复时间范围的时间
    例子
    SELECT MIN(cykssj), MAX(cyjssj)
    FROM (SELECT cykssj,
    cyjssj,
    SUM(broken) OVER(ORDER BY cykssj, cyjssj) flag
    FROM (SELECT t.*,
    (CASE
    WHEN cykssj <= MAX(cyjssj)
    OVER(ORDER BY cykssj,
    cyjssj ROWS BETWEEN UNBOUNDED
    PRECEDING AND 1 PRECEDING) THEN
    0
    ELSE
    1
    END) AS broken
    FROM fw_zjjh t))
    GROUP BY flag;

  21. 分级函数
    例子
    select ZZMC,LEVEL from xt_zz
    WHERE ROWNUM<2
    start WITH zzdm=’01’ –从哪个级别代码查询
    connect by prior zzdm = sjdm–分级的条件

  22. 判断某一个字符串是否包含某个字符串或者字符
    例子
    –查询xxmc中包括工单两个字的xxmc
    select c.xxmc rom kfgdxx c where instr(c.xxmc,’工单’)>0

  23. with as 用法
    select * from
    (

         SELECT LEVEL AS lv
           FROM DUAL
     CONNECT BY LEVEL < 20
    

    ) tt
    WHERE tt.lv > 10 AND tt.lv < 15
    –优化后
    with TT as(

             SELECT LEVEL AS lv
             FROM DUAL
            CONNECT BY LEVEL < 20
         ) 
    

    select lv from TT
    WHERE lv > 10 AND lv < 15

  24. 按照列合计
    例子
    select nvl(to_char(t.cjsj,’MM’),’合计’),sum(t.hwl) ,sum(t.zhwl) from FW_TYSL_95598 t GROUP BY ROLLUP(to_char(t.cjsj,’MM’))

  25. 列转行用sum 与group by 和listagg group by都可以

  26. 如果你遇到列和行都是动态改变的,可以有两种方式实现,可以把列先查出来,存入一个临时表,再把行查出来存入另一个临时表。最后进行双层循环,就可以实现。另一种方法,就是在前台实现行的动态改变,在数据中实现列的改变

  27. 例子
    1 A B
    2 B C
    3 B A
    应该显示
    1 A B
    2 B C
    –实现的原理
    select count(1)from(
    select 2||1 from dual
    union
    select 1||2 from dual
    minus
    select 1||2 from dual)

  28. 子查询,嵌套查询相当于外连接,但比外连接效率高

查看评论

电脑常见故障---常见的硬件故障解决方案

  • 2010年11月15日 15:05
  • 622KB
  • 下载

Oracle查询性能优化(面试题:数据库查询优化也是常问的问题)

原则一:注意WHERE子句中的连接顺序:  ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WH...
  • hxpjava1
  • hxpjava1
  • 2017-02-21 11:40:06
  • 1431

oracle数据库优化

  • 2011年09月09日 09:09
  • 1.11MB
  • 下载

在CentOS下使用ngx_http_google_filter_module

安装Git&GCC yum -y update && yum -y groupinstall "Development Tools" && yum -y install git gcc make e...
  • www203203
  • www203203
  • 2017-07-28 22:38:41
  • 525

JAVA常见异常 几种常见异常剖析与记录

记录自己常遇到的异常和网上找的java中的一些常见异常,方便以后遇到时找到原因并解决。 java.lang.NullPointerException 空指针异常。当应用试图在要求使用对...
  • Krito_blog
  • Krito_blog
  • 2017-01-31 16:25:41
  • 687

MySQL/Oracle数据库优化总结(非常全面)

MySQL/Oracle数据库优化总结(非常全面)
  • baidu_37107022
  • baidu_37107022
  • 2017-08-21 21:05:30
  • 3507

计算机技术教程(txt)

  • 2008年03月14日 22:24
  • 263KB
  • 下载

Oracle数据库优化的总结

总结和记录了Oracle数据库SQL优化的方法和创建索引的建议。
  • dayu236
  • dayu236
  • 2016-09-25 15:36:08
  • 1440

oracle 数据库优化

  • 2009年06月04日 21:35
  • 13KB
  • 下载

“运行错误-不能识别对象”的处理方法.doc

  • 2010年05月17日 14:35
  • 43KB
  • 下载
    个人资料
    等级:
    访问量: 2985
    积分: 273
    排名: 28万+
    文章分类
    友情链接