oracle数据库sql的优化总结

一：使用where少使用having;

二：查两张以上表时，把记录少的放在右边；

三：减少对表的访问次数；

四：有where子查询时，子查询放在最前；

五：select语句中尽量避免使用*（执行时会把*依次转换为列名）；

六：尽量多的使用commit；

七：Decode可以避免重复扫描相同的记录或重复连接相同的表；

八：通过内部函数也可提高sql效率；

九：连接多个表时，使用别名并把别名前缀于每个字段上；

十：用exists代替in

十一：not exists代替 not in（not in 字句将执行一个内部的排序和合并，任何情况下，not in是最低效的，子查询中全表扫描了。为了避免使用not in，可以改写成outer joins或not exists）;

十二：表连接比exists更高效；

十三：用exists替换distinct

例：

低：                                                                    高：

select distinct dept_no, dept_name                        select dept_no, dept_name

from dept d, emp e                                               from dept d

where d.dept_no = e.dept_no;                               where exists (select 1 from emp e where e.dept_no = d.dept_no);

十四：使用TKPROF工具来查询sql性能状态；

十五：用索引提高效率（代价是：索引需要空间，而且定期重构索引很有必要：ALTER INDEX<INDEXNAME> REBUILD<TABLESPACENAME）；

先介绍下索引的原理，方便接下来对索引的优化的理解：

通过索引找到rowid，然后通过rowid访问表。但如果查询的列包括在index中，将不在执行第二部操作，因为检索数据保存在索引中，单单访问索引就可以完全满足查询要求。

前提提要：在十六例中，LODGING列有唯一索引；MANAGER列上有非唯一性索引。

十六：索引范围查询（INDEX RANGE SACEN):

适用于两种情况：

1）基于一个范围的查询：

SELECT LODGING FROM LODGING WHERE LODGING LIKE 'M%'

（where字句条件包括一系列的值，oracle将通过索引范围查询方式查询LODGING_PK）

2) 基于非唯一性索引的检索：

SELECT LODGING FROM LODGING WHERE MANAGER = 'LI';

(此查询分两步：LODGING$MANAGER的索引范围查询得到所有符合条件记录的rowid，然后通过rowid访问表得到LODGING列的值。该索引为非唯一性索            引，数据库不能对它执行索引唯一扫描）

where字句中，如果索引列所对应的值的第一个字符由通配符开始，索引将不被采用，而会全表扫描，如 SELECT..... WHERE MANAGER LIKE '%LI'

十七：基础表的选择：

基础表：最先访问的表（通常以全表扫描的方式被访问）。

根据优化器的不同，SQL语句中基础表的选择是不一样的：

如果使用CBO，优化器会检查SQL语句中的每个表的物理大小，索引的状态，然后选用话费最低的路径。

如果使用RBO，并且所有的连接条件都有索引对应，这种情况下基础表就是FROM字句中列在最后的表

例：

SELECT A.NAME, B.MANAGER FROM WOKER A, LODGING B WHERE A.LODGING = B.LODGING;

由于LODGING列上有一个索引，而且WORKER表中没有相比较的索引，WORKER表将被作为查询基础表。

十八：多个平等的索引：

当SQL语句的执行路径可以使用分布在多个表上的多个索引时，oracle会同事使用多个索引并在运行时对它们的记录合并，检索仅对全部索引有效的记录。

oracle选择执行路径是，唯一索引等级高于非唯一索引，只有当where字句中索引列和常量比较才有效。如果索引列和其它表的索引列相比较，这种字句在优化器中等级非常低；

如果不同表中两个相同等级的索引将被引用，根据FROM字句中表的顺序决定哪个先被使用。FROM字句中最后的表索引优先级高。如果相同表中两个相同等级的索引将被引用，where字句中最先被引用的索引将有最高的优先级。

例：DEPTNO上有非唯一性索引，EMP_CAT也有非唯一性索引

SELECT ENAME FROM EMP WHERE DEPT_NO = 20 AND EMP_CAT = 'A';

DEPTNO索引将被先检索，然后同EMP_CAT索引检索出的结果合并，执行路径如下：

TABLE ACCESS BY ROWID ON EMP

AND _EQUAL

INDEX RANGE SCAN ON  DEPT_IDX

INDEX RANGE SCAN ON CAT_IDX

十九：等式比较与范围比较：

先上例子：

SELECT ENAME FROM EMP WHERE DEPT_NO > 20 AND EMP_CAT = 'A';

（在两个非唯一性索引前提下）此时范围索引不被使用，通过EMP_CAT索引查询出记录再与DEPT_NO条件进行比较

注意：唯一性所以做范围比较时，等级要比非唯一性索引的等式比较低；

二十：强制索引失效：

如果两个或两个以上索引具有相同的等级，可以强制命令oracle优化器使用其中的一个。 那何时使用此种策略呢？如果一个索引已接近于唯一，而另一索引有很多重复的值，排序与合并反而会成为负担，此时可以屏蔽后者使其索引失效。

（失效方式：对索引列加入计算'+0'或'||""'）;

赞 (0)

相关推荐

  • Oracle SQL性能优化系列学习三

    正在看的ORACLE教程是:Oracle SQL性能优化系列学习三.8. 使用DECODE函数来减少处理时间 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表. 例如: SELECT COUNT(*),SUM(SAL) FROM EMP  WHERE DEPT_NO = 0020  AND ENAME LIKE 'SMITH%'; SELECT COUNT(*),SUM(SAL)  FROM EMP  WHERE DEPT_NO = 0030  AND ENAME LIKE 'SM

  • ORACLE SQL语句优化技术要点解析

    操作符优化: IN 操作符 用IN写出来的SQL的优点是比较容易写及清晰易懂,这比较适合现代软件开发的风格. 但是用IN的SQL性能总是比较低的,从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别: ORACLE试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询. 由此可见用IN的SQL至少多了一个转换的过程.一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了. 推荐方案:

  • Oracle SQL性能优化系列学习二

    正在看的ORACLE教程是:Oracle SQL性能优化系列学习二.  4. 选择最有效率的表名顺序(只在基于规则的优化器中有效) ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(

  • Oracle SQL性能优化系列学习一

    正在看的ORACLE教程是:Oracle SQL性能优化系列学习一.1. 选用适合的ORACLE优化器 ORACLE的优化器共有3种: a. RULE (基于规则) b. COST (基于成本) c. CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你当然也在SQL句级或是会话(session)级对其进行覆盖. 为了使用基于成本的优化器(CBO,

  • oracle下一条SQL语句的优化过程(比较详细)

    原来的语句是这样的: select sum(sl0000) from xstfxps2 where dhao00 in ( select dhao00 from xstfxps1 where trunc(ywrq00)=trunc(sysdate) and khdm00='500000003913'); 已用时间: 00: 02: 49.04 Execution Plan ---------------------------------------------------------- 0 S

  • Oracle SQL tuning 数据库优化步骤分享(图文教程)

    Oracle SQL tuning 数据库优化步骤分享(图文教程)

    SQL Turning 是Quest公司出品的Quest Central软件中的一个工具.Quest Central是一款集成化.图形化.跨平台的数据库管理解决方案,可以同时管理 Oracle.DB2 和 SQL server 数据库. 一.SQL Tuning for SQL Server简介 SQL语句的优化对发挥数据库的最佳性能非常关键.然而不幸的是,应用优化通常由于时间和资源的因素而被忽略.SQL Tuning (SQL优化)模块可以对比和评测特定应用中SQL语句的运行性能,提出智能化的

  • Oracle 表三种连接方式使用介绍(sql优化)

    1. NESTED LOOP 对于被连接的数据子集较小的情况,nested loop连接是个较好的选择.nested loop就是扫描一个表,每读到一条记录,就根据索引去另一个表里面查找,没有索引一般就不会是 nested loops.一般在nested loop中, 驱动表满足条件结果集不大,被驱动表的连接字段要有索引,这样就走nstedloop.如果驱动表返回记录太多,就不适合nested loops了.如果连接字段没有索引,则适合走hash join,因为不需要索引. 可用ordered提

  • oracle数据库sql的优化总结

    一:使用where少使用having; 二:查两张以上表时,把记录少的放在右边: 三:减少对表的访问次数: 四:有where子查询时,子查询放在最前: 五:select语句中尽量避免使用*(执行时会把*依次转换为列名): 六:尽量多的使用commit: 七:Decode可以避免重复扫描相同的记录或重复连接相同的表: 八:通过内部函数也可提高sql效率: 九:连接多个表时,使用别名并把别名前缀于每个字段上: 十:用exists代替in 十一:not exists代替 not in(not in 字

  • 数据库SQL语句优化总结(收藏)

    网上关于SQL优化的教程很多,但是比较杂乱.近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充. 这篇文章我花费了大量的时间查找资料.修改.排版,希望大家阅读之后,感觉好的话推荐给更多的人,让更多的人看到.纠正以及补充. 1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id f

  • 数据库sql语句优化

    为什么要优化: 随着实际项目的启动,数据库经过一段时间的运行,最初的数据库设置,会与实际数据库运行性能会有一些差异,这时我们 就需要做一个优化调整. 数据库优化这个课题较大,可分为四大类: >主机性能 >内存使用性能 >网络传输性能 >SQL语句执行性能[软件工程师] 下面列出一些数据库SQL优化方案: (01)选择最有效率的表名顺序(笔试常考) 数据库的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表将被最先处理,在FROM子句中包含多个表的情况下,你

  • oracle数据库优化辅助SQL语句

    具体详情请看下文代码分析. --查询正在执行的SQL语句 SELECT OSUSER 电脑登录身份, PROGRAM 发起请求的程序, USERNAME 登录系统的用户名, SCHEMANAME, B.Cpu_Time 花费cpu的时间, STATUS, B.SQL_TEXT 执行的sql, B. FROM V$SESSION A LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS AND A.SQL_HASH_VALUE = B.HASH_VALUE

  • Oracle数据库性能优化技术开发者网络Oracle

    正在看的ORACLE教程是:Oracle数据库性能优化技术开发者网络Oracle.介绍:细处着手,巧处用功.高手和菜鸟之间的差别就是:高手什么都知道,菜鸟知道一些.电脑小技巧收集最新奇招高招,让你轻松踏上高手之路.  摘要: Oracle数据库是当前应用最广泛的大型数据库之一,而其性优化直接关系到系统的运行效率.本文以数据库性能优化的基本原则为出发点,阐述了在数据库设计阶段如何避免竞争和如何优化数据访问,在数据库运行阶段如何从操作系统和数据库实例级别上调整内存和I/O来达到数据库性能优化的各种技

  • Oracle数据库中SQL语句的优化技巧

    在SQL语句优化过程中,我们经常会用到hint,现总结一下在SQL优化过程中常见Oracle HINT的用法: 1. /*+ALL_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化. 例如: SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT'; 2. /*+FIRST_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.

  • Oracle数据库及应用程序优化开发者网络Oracle

    正在看的ORACLE教程是:Oracle数据库及应用程序优化开发者网络Oracle.介绍:细处着手,巧处用功.高手和菜鸟之间的差别就是:高手什么都知道,菜鸟知道一些.电脑小技巧收集最新奇招高招,让你轻松踏上高手之路. 摘 要:本文对ORACLE数据库及ORACLE应用程序的优化,进行了全面的分析与研究,并提出了自己的一些建议. 关 键 词:ORACLE,优化,数据库,SQL 1.引言 随着信息化时代的到来,人们开始广泛地使用数据库技术对大量而复杂的信息进行科学高效的管理.在数据库领域中的各种应用

  • Oracle数据库优化策略总结篇

    为了提高查询效率,我们常常做一些优化策略.本文主要介绍一些Oracle数据库的一些不常见却是非常有用的优化策略,希望能对您有所帮助. SQL语句优化 这个好办,抓到挪借CPU高的SQL语句,依据索引.SQL技巧等修改一下,行之管用. SELECT时不利用函数 在做频繁的查询垄断时,尽量直接select字段名,然后利用C语言代码对查询收获做二次加工,避免让Oracle来做混杂的函数可能数学计算.因为Oracle出于通用性的琢磨,其函数及数学计算的速度远不及用C语言直接编译成机器码后计算来的快. 绑

随机推荐