当前位置：文档之家 › 基于Oracle 的SQL优化

基于Oracle 的SQL优化

  • 格式：pdf
  • 大小：294.24 KB
  • 文档页数：24

下载文档原格式

  / 24

合集下载

OracleSQL性能优化及案例分析

OracleSQL性能优化及案例分析

OracleSQL性能优化及案例分析标题：Oracle SQL性能优化及案例分析一、引言Oracle数据库作为全球最受欢迎的数据库之一，其性能优化问题一直是用户和开发者的焦点。

尤其是在处理大量数据或复杂查询时，性能问题可能会严重影响应用程序的响应时间和用户体验。

因此，对Oracle SQL进行性能优化及案例分析显得尤为重要。

二、Oracle SQL性能优化1、索引优化索引是提高Oracle SQL查询性能的重要工具。

通过创建合适的索引，可以大大减少查询所需的时间，提高数据库的响应速度。

然而，过多的索引可能会导致额外的存储空间和插入、更新、删除的性能损失。

因此，需要根据实际应用的需求，合理地选择需要索引的字段。

2、查询优化编写高效的SQL查询语句也是提高Oracle SQL性能的关键。

这包括选择正确的查询语句、避免在查询中使用复杂的子查询、使用连接（JOIN）代替子查询等。

还可以使用Oracle SQL Profiler来分析和优化查询语句的性能。

3、数据库参数优化Oracle数据库有许多参数可以影响SQL性能，如内存缓冲区、磁盘I/O参数等。

根据实际应用的需求和硬件环境，对这些参数进行合理的调整，可以提高Oracle SQL的性能。

三、案例分析1、案例一：索引优化问题描述：在一个电商系统中，用户在搜索产品时，使用全文本搜索功能时经常出现延迟。

解决方案：通过分析用户搜索的习惯和需求，对产品表的名称和描述字段创建全文索引。

同时，调整Oracle的全文搜索参数以提高搜索效率。

2、案例二：查询优化问题描述：在一个银行系统中，客户查询自己的贷款信息时，查询时间过长。

解决方案：通过使用Oracle SQL Profiler分析查询语句，发现查询中存在复杂的子查询。

将子查询改为连接（JOIN）方式，减少了查询时间。

3、案例三：数据库参数优化问题描述：在一个大型电商系统中，用户在访问高峰期经常遇到响应时间过长的问题。

ORACLE优化SQL语句,提高效率

ORACLE优化SQL语句,提高效率

ORACLE优化SQL语句，提高效率我们要做到不但会写SQL，还要做到写出性能优良的SQL语句。

（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)：Oracle的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,您必须选择记录条数最少的表作为基础表。

假如有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表。

（2）WHERE子句中的连接顺序：Oracle采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些能够过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

（3）SELECT子句中避免使用‘*’：Oracle在解析的过程中, 会将‘*’依次转换成任何的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。

（4）减少访问数据库的次数：Oracle在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等。

（5）在SQL*Plus , SQL*Forms和Pro*C中重新配置ARRAYSIZE参数, 能够增加每次数据库访问的检索数据量 ,建议值为200。

（6）使用DECODE函数来减少处理时间：使用DECODE函数能够避免重复扫描相同记录或重复连接相同的表。

（7）整合简单,无关联的数据库访问：假如您有几个简单的数据库查询语句,您能够把他们整合到一个查询中(即使他们之间没有关系)。

（8）删除重复记录：最高效的删除重复记录方法 ( 因为使用了ROWID)例子：DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);（9）用TRUNCATE替代DELETE：当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放能够被恢复的信息. 假如您没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息。

ORACLE数据库SQL语句优化讲稿

ORACLE数据库SQL语句优化讲稿

ORACLESQL语句优化SQL Select语句完整的执行顺序1、from子句组装来自不同数据源的数据；2、where子句基于指定的条件对记录行进行筛选；3> group by子句将数据划分为多个分组；4、使用聚集函数进行计算；5、使用having 了-句筛选分组；6、计算所有的表达式；7、使用order by对结果集进行排序。

说明：—8) SELECT (9) DISTINCT (11) <T0P_spec辻ication> <select_list>—(1) FROM <left_table>—(3) <join_type> JOIN <right_table>—(2) ON <join_condition>—(4) WHERE <where_condit ion>—(5) GROUP BY <group_by_list>—(6) WITH {CUBE ROLLUP}—(7) HAVING <having_condition>~(10) ORDER BY <order_by_list>Oracle SQL性能优化技巧1、选用适合的ORACLE优化器2、访问Table的方式3、共享SQL语句4、选择最有效率的表名顺序(只在基于规则的优化器中有效)5、 WHERE子句中的连接顺序6、SELECT子句中避免使用'* '7、减少访问数据库的次数8、使用DECODE函数来减少处理时间9、整合简单，无关联的数据库访问10、删除重复记录11、用 TRUNCATE 替代 DELETE12、尽量多使用COMMIT13、计算记录条数14、用Where子句替换HAVING子句13、减少对表的查询16、通过内部函数提高SQL效率17、使用表的别名(Alias)18、用 EXISTS 替代 IN19、用 NOT EXISTS 替代 \0T IN20、用表连接替换EXISTS21、用 EXISTS 替换 DISTINCT1.选用适合的ORACLE优化器ORACLE的优化器共有3种a、 RULE (基于规则)b、 COST (基于成本)c、 CHOOSE (选择性)设置缺省的优化器，可以通过对init. ora文件中OPTIMIZER.MODE参数的各种声明，如 RULE, COST, CHOOSE, ALL_ROWS, FIRST_ROWS。

oracle sql 优化技巧

oracle sql 优化技巧

oracle sql 优化技巧（实用版3篇）目录（篇1）1.Oracle SQL 简介2.优化技巧2.1 减少访问数据库次数2.2 选择最有效率的表名顺序2.3 避免使用 SELECT2.4 利用 DECODE 函数2.5 设置 ARRAYSIZE 参数2.6 使用 TRUNCATE 替代 DELETE2.7 多使用 COMMIT 命令2.8 合理使用索引正文（篇1）Oracle SQL 是一款广泛应用于各类大、中、小微机环境的高效、可靠的关系数据库管理系统。

为了提高 Oracle SQL 的性能，本文将为您介绍一些优化技巧。

首先，减少访问数据库的次数是最基本的优化方法。

Oracle 在内部执行了许多工作，如解析 SQL 语句、估算索引的利用率、读数据块等，这些都会大量耗费 Oracle 数据库的运行。

因此，尽量减少访问数据库的次数，可以有效提高系统性能。

其次，选择最有效率的表名顺序也可以明显提升 Oracle 的性能。

Oracle 解析器是按照从右到左的顺序处理 FROM 子句中的表名，因此，合理安排表名顺序，可以减少解析时间，提高查询效率。

在执行 SELECT 子句时，应尽量避免使用，因为 Oracle 在解析的过程中，会将依次转换成列名，这是通过查询数据字典完成的，耗费时间较长。

DECODE 函数也是一个很好的优化工具，它可以避免重复扫描相同记录，或者重复连接相同的表，提高查询效率。

在 SQLPlus 和 SQLForms 以及 ProC 中，可以重新设置 ARRAYSIZE 参数。

该参数可以明显增加每次数据库访问时的检索数据量，从而提高系统性能。

建议将该参数设置为 200。

当需要删除数据时，尽量使用 TRUNCATE 语句替代 DELETE 语句。

执行 TRUNCATE 命令时，回滚段不会存放任何可被恢复的信息，所有数据不能被恢复。

因此，TRUNCATE 命令执行时间短，且资源消耗少。

在使用 Oracle 时，尽量多使用 COMMIT 命令。

OracleSQL性能优化

OracleSQL性能优化

AND EXISTS (SELECT ‘X’ FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)
2003-09
21
用NOT EXISTS替代NOT IN (1)
➢ 在子查询中，NOT IN子句将执行一个内部的排序和 合并，对子查询中的表执行一个全表遍历，因此是非 常低效的。
WHERE DEPT_CAT=’A’）
高效：
SELECT …. FROM EMP E
WHERE NOT EXISTS （SELECT ‘X’ FROM DEPT D
WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A’）
2003-09
23
用表连接替换EXISTS
2003-09
4
选择最有效率的表名顺序（1）
➢ ORACLE的解析器按照从右到左的顺序处理FROM子句中的 表名，因此FROM子句中写在最后的表(基础表 driving table) 将被最先处理
➢ 当ORACLE处理多个表时，会运用排序及合并的方式连接它 们。首先，扫描第一个表(FROM子句中最后的那个表)并对记 录进行派序，然后扫描第二个表(FROM子句中最后第二个表)， 最后将所有从第二个表中检索出的记录与第一个表中合适记 录进行合并.
SELECT … FROM EMP E
WHERE SAL > 50000 AND JOB = ‘MANAGER’ AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR = E.EMPNO)
(高效,执行时间10.6秒)MP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
oracle Sql语句优化原则

oracle Sql语句优化原则

S ql语句优化原则一、优化原则1、避免使用硬编码，改用绑定变量实现。

举例：String str =’ select * from t_zx_ryjbxxb where xm=’+params;上面这条语句使用了硬编码，使用这种方式存在两方面问题：每次执行sql语句时都需要重新解析sql语句；可能会遭遇sql注入攻击。

如在上面的语句中输入张三' or 1='1，则会把所有的记录都显示出来。

解决的方法就是使用占位符代替硬编码。

如下：String str =’select * from t_zx_ryjbxxb where xm=?’;2、当插入的数据为数据表中的记录数量的10%以上，首先需要删除该表的索引来提高数据的插入效率，当数据插入后，再建立索引。

3、避免在索引列上使用函数或计算，在where子句中，如果索引是函数的一部分，优化器将不再使用索引而使用全表扫描。

举例：低效：select * from dept where sal*12 >2500;高效：select * from dept where sal>2500/12;4、避免在索引列上使用not和“!=”，索引只能告诉什么存在于表中，而不能告诉什么不存在于表中，当数据库遇到not 和“!=”时，就会停止使用索引而去执行全表扫描。

5、使用关联查询替代in ，可以提高查询的效率。

6、使用not exists子查询替代not in。

在子查询中，NOT IN子句将执行一个内部的排序和合并。

无论在哪种情况下，NOT IN都是最低效的（因为它对子查询中的表执行了一个全表遍历）。

为了避免使用NOT IN ，我们可以把它改写成外连接（Outer Joins）或NOT EXISTS.举例：select * from t_zx_ryjbxxb where rybh not in(select rybh from t_zx_cqrb) and jwh=''select * from t_zx_ryjbxxb a where not exists(select1from t_zx_cqrb b where a.rybh =b.rybh)使用union-all 替代union：当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并，然后在输出最终结果前进行排序。

Oracle数据库的sql语句性能优化

Oracle数据库的sql语句性能优化

Oracle数据库的sql语句性能优化 在应⽤系统开发初期，由于开发数据库数据⽐较少，对于查询sql语句，复杂试图的编写等体会不出sql语句各种写法的性能优劣，但是如果将应⽤系统提交实际应⽤后，随着数据库中数据的增加，系统的响应速度就成为⽬前系统需要解决的最主要问题之⼀。

系统优化中⼀个很重要的⽅⾯就是sql语句的优化。

对于海量数据，劣质sql语句和优质sql语句之间的速度差别可以达到上百倍，可见对于⼀个系统不是简单地能实现其功能就⾏，⽽是要写出⾼质量的sql语句，提⾼系统的可⽤性。

Oracle的sql调优第⼀个复杂的主题，甚⾄需要长篇概论来介绍OracleSQL调优的细微差别。

不过有⼀些基本的规则是每个OracleDBA都需要遵从的，这些规则可以改善他们系统的性能。

sql调优的⽬标是简单的：消除不必要的⼤表全表搜索。

不必要的全表搜索导致⼤量不必要的磁盘I/O，从⽽拖慢整个数据库的性能，对于不必要的全表搜索来说，最常见的调优⽅法是增加索引，可以在表中加⼊标准的B树索引，也可以加⼊位图索引和基于函数的索引。

要决定是否消除⼀个全表搜索，你可以仔细检查索引搜索的I/O开销和全表搜索的开销，它们的开销和数据块的读取和可能的并⾏执⾏有关，并将两者作对⽐。

另外，在全表搜索是⼀个最快的访问⽅法时，将⼩表的全表搜索放到缓存（内存）中，也是⼀个⾮常明智的选择。

我们会发现现在诞⽣了很多基于内存的数据库管理系统，将整个数据库置于内存之中，性能将得到质的飞跃。

⼀、与索引相关的性能优化 在多数情况下，Oracle使⽤索引来更快地遍历表，优化器主要根据定义的索引来提⾼性能。

但是，如果在sql语句的where⼦句中写的sql代码不合理，就会造成优化器删去索引⽽使⽤全表扫描，⼀般这种sql语句就是所谓的劣质sql语句。

在编写sql语句时我们应清楚优化器根据何种原则来删除索引，这有助于写出⾼性能的sql语句。

1.IS NULL 与 IS NOT NULL 不能⽤null做索引，任何包含null值的列都将不会被包含在索引中，即使索引有多列这样的情况下，只要这些列中有⼀列含有null，该列就会从索引中排除。

基于Oracle 的sql优化

基于Oracle 的sql优化

select count(distinct column_name),count(*) total_rows, count(distinct column_name) / count(*) * 100 selectivity
from table_name;-----表的segment很大，尤其超过SGA慎用 统计信息准确的情况下可以利用数据字典查询eg：dba_tab_col_statistics a, dba_tables
执行计划
在做SQL优化的时候，都必须查看执行计划，看执行计划的时候一定要用 SQLPLUS去看，千万不要用PL/SQL /TOAD/EM等工具去看执行计划。因为这 些工具只能看到访问路径，无法看到谓词过滤信息，而恰巧，谓词过滤信息是 执行计划中的重中之重
1、利用AUTOTRACE查看执行计划 set autot on ----执行SQL 并且显示执行计划和统计信息 set autot trace ----执行SQL 但不显示运行结果，显示执行计划和统计信息 set autot trace exp ----如果SELECT 就不执行SQL(dml 执行)，只显示执行计 划 set autot trace stat ----执行SQL，只显示统计信息
SQL> select * from table(dbms_xplan.display); 1）TABLE ACCESS FULL 前面有 * 表示什么 2）看执行计划看到全表扫描前面有* 要怎么样 3）table access full 前面 没* 咋办
1. 检查SQL语句 2. 检查表大不大
JOIN列 + select 列 4）TABLE ACCESS BY INDEX ROWID 前面有*怎么办？ 例：create table test as select * from dba_objects; create index idx on test(object_id); explain plan for select * from test where object_id=10 and object_name='SCOTT'; 然后查看执行计划
基于ORACLE数据库的SQL优化研究

基于ORACLE数据库的SQL优化研究

关 键 词 ：ＯＲＡＣ Ｌ Ｅ数 据 库 ：Ｓ ＱＬ优 化
中图分类号 ：Ｔ Ｐ ３ １ １ ． １ ３ － ７ ７ １ ２（ ２ ０ １ ３ ）１ ０ — ０ １ ０ ２ — ０ Ｉ
在 Ｏ Ｒ Ａ Ｃ Ｌ Ｅ数据库中，Ｄ Ｄ Ｌ语句可 以实现 自动提交，而 其 他 诸如 Ｄ Ｍ Ｌ之类 的多为手 工提 交或 回滚事务 。 在编写应 用程序 时，缓存 中可能会有一些数据没来得及修改 ， 尤其是遇 到较 为 复杂的操作 时，如 ｄ ｅ ｌ ｅ ｔ ｅ 、ｕ ｐ ｄ ａ ｔ ｅ 等 ，应 该恰 当地选择 Ｃ Ｏ Ｍ Ｍ Ｉ Ｔ来完成事务 的提交工作 ，以清理缓存中的垃圾 ，释 放系统 资源，提升 系统 的性 能。 （ 四）连接顺序 在 查询 过程 中，可能会遇 到对 多个表格进行操作 的情况 ， 此 时 ，应先选择交叉表 （ 即被其他表引用的基础表 ） 。在 Ｒ Ｒ Ｏ Ｍ 语 句中，表的顺序和连接的效果有 着直 接联 系，一般而言 ，０ Ｒ Ａ Ｃ Ｌ Ｅ解 析器在处理这 些表 时 ，是按照 从右到左的顺序进 行 的，而且，不同的优 化器会 带来 不一样 的选择 。若是 Ｃ Ｂ Ｏ ， 优 化 器会对表 的索引状 态以及物 理大 小进行检查 ， 最终选 择花 费 较 低的一种 ；若 是 Ｒ Ｂ Ｏ ，当所有 的连接都有相对应的索引时， Ｒ Ｒ Ｏ Ｍ子句 中最后的表即是基 础表 。 （ 五）Ｗ Ｈ Ｅ Ｒ Ｅ子句 的顺序 者尤为关键 ， 消耗 的数 据库 资源 最大 。 因此 ， 为 了保证数据库 在 Ｏ ＡＣ Ｒ Ｌ Ｅ解析器对 Ｗ Ｈ Ｅ Ｒ Ｅ子句进行解析时，所采 用的次 系统的高效正常运行 ，必须对 Ｓ Ｑ Ｌ语句进行优化 。 序 通常是 自下而 上，因此 ， 索引列一般都 是写在最前面 ， 而 能 二、Ｓ Ｏ Ｌ优 化 的 目的 够 将大 量记 录过 滤掉 的条件应在 Ｗ Ｈ Ｅ Ｒ Ｅ子句 的末尾 。 对于一个正常运行的数据库 ，一旦 Ｓ Ｑ Ｌ的结构设计不合 （ 六）Ｅ Ｘ Ｉ Ｓ Ｔ Ｓ的使用 理，极有 可能引起 系统 的性 能问题。对 Ｓ Ｏ Ｌ进行调整 ，实际就 在 查询 中， 如果有多个基础表， 则常 常需要连接另一个表 。 是将繁杂的数据简单化，常用 的途径就是对 Ｓ Ｑ Ｌ语句 的调整 ， Ｉ Ｎ子查询 中的表是通过 Ｏ Ｒ Ａ Ｃ Ｌ Ｅ 全表扫描过的 ， 查询效率较低 ， 从而提升系统性能。实现 Ｓ Ｑ Ｌ的优化，充分发挥索引 的功能 ， 如 果换 做 Ｅ Ｘ Ｉ Ｓ Ｔ Ｓ ，能够 很明显的提升 查询效率 。 将 表扫描的次数降到最低 ， 主要是为 了助优化器将劣质 的 Ｓ Ｑ Ｌ （ 七）在索 引上避免使用计算 语 句向优质的 Ｓ Ｑ Ｌ语句转换 ， 保持查找数据的路径处于最佳状 在Ｗ Ｈ Ｅ Ｒ Ｅ 子 句中， 如果 引用到 函数计算 ， 且索引列 属于 函 态 ，使 Ｃ Ｐ Ｕ时间和 Ｉ ／ Ｏ时间尽量维持平衡 。在优化过程 中，要 数 的组 成部 分， 鉴于在索 引上尽量不使用计算 ， 所 以常常会 丢 学会对 Ｓ Ｑ Ｌ语句的挑选 ， 通常是挑些性能有很大提升空 间， 或 弃 索引而选择全表扫面 。 通过相关实验也可发现， 进行优化后 ， 出现频率较多，消耗资源也是最多 的语句进行优化 。 命 中率 有了很大提高 ，Ｃ Ｐ Ｕ得到改善 ，整个系统的性能进一 步 三 、 优 化 策 略 高。 四 、结 束语 随着数据库的规模越来越大 ， 存储量也不断增加，通过实 验 可知，高效的 Ｓ Ｑ Ｌ语句和低效的相 比，其差别是非常之大 。 如今 ， 人们对信息 的需求量越来越多 ， 数据库 也不 断扩大 在Ｏ Ｒ Ａ Ｃ Ｌ Ｅ数据 库中，影响 Ｓ Ｏ Ｌ查询的因素主要有 以下三个： 改善，同时，对数据库 的优 化工 作就显得十分关键 ， 它直接 决 是内存，二是 Ｃ Ｐ Ｕ ，三是 Ｉ ／ ０次数 。因此对 Ｓ Ｑ Ｌ进行优化就 定着数据库 系统 的运行状态和工作效率 ， 因此，必须做好系统 是减少 内存的 占用时间 ， 降低 Ｃ Ｐ Ｕ的计算频率 ， 缩减 Ｉ ／ ０总次 的优化 工作 。 数。 参考文 献： （ 一 ） 索 引 的 建 立 Ⅲ郭珉． ＯＲ Ａ Ｃ Ｌ Ｅ数据库 Ｓ ＱＬ优化原 则Ⅱ １ ． 计 算机 系统应 在Ｓ Ｑ Ｌ查询优化中 ， 索引是必不可少的一部分 ， 起着 引导 用 ， ２ ０ １ ０ ， ２ ７ ＠） ： １ ８ ７ — １ ８ ９ ． 作用 。在其 正确 引导下 ，可大大减少表搜索的访 问次数 ，提高 【 ２ 】 郭 霞． 基 于 ＯＲ ＡＣ Ｌ Ｅ数据库 的 Ｓ ＱＬ语 句优化分析 Ⅱ １ ． 电 查询速度 。在使用过程中，应按照一定的原则进行，而且，索 脑 知 识 与技 术， ２ ０ １ １ ， １ ７ （ ２ １ ） ： ２ １ ３ — ２ １ ５ ． 引和 用 户 表 可 能 会 发 生 某 些 竞 争 ， 所 以为 避 免 此现 象 ，二 者 不 ［ ３ １ 江骏 ． Ｏｒ ａ ｃ ｌ ｅ中 Ｓ ＱＬ优化原理分析 ［ Ｉ ］ ． 计算机教 育， ２ ０ ０ ９ ， 可在 同一磁盘上建立 。 ２ ６ （ １ ０ ） ： １ ６ ２ — １ ６ ４ ． （ 二 ）‘ ’ 的 使 用 ［ ４ ］ 张学义， 王观 玉， 黄隽． 基 于 Ｏｒ ａ ｌ ｃ ｅ 数 据库 Ｓ ＱＬ查询优化 如 果是 Ｓ Ｅ Ｌ Ｅ Ｃ Ｔ子 句 ，应 尽 量 不要 使 用 ‘ ， ｌ ｃ ’ 。因 为在 Ｏ ＡＣ Ｒ 研 究 ｍ． 制 造 业 自动 化 ， ２ ０ １ １ ， ２ ６ （ ２ ） ： １ ４ ２ － １ ４ ４ ． Ｌ Ｅ解析过程 中，‘ ｛ ’会和表 中列名之 间发 生相应 的转换，这 个过程 十分复杂，涉及 的方面很 多，需要对数据库 的所有 字典 ［ 基 金项 目］ 此论文为深圳信息职业技术学院 ２ ０ １ ０年度 院 基 于智 能语义分析技术 的跨平 台 Ｓ Ｑ Ｌ解析 进行 查询 ，不但工作量大 ，而且消耗 了大量的时间 ， 但 效果却 内一般科研项 目，《 非常低 下。 系统 的研究与应用》（ 编 号：Ｙ Ｂ ２ ０ １ ０ １ ４ ）及横 向科研项 目 《 植 （ 三 ）Ｃ Ｏ ⅢＩ Ｔ的 使 用 物标本馆标本信息数据库》（ 编 号：Ｈ Ｘ — Ｏ ３ ５ ）研究成果 。
oracle sql优化常用的15种方法

oracle sql优化常用的15种方法

oracle sql优化常用的15种方法1. 使用合适的索引索引是提高查询性能的重要手段。

在设计表结构时，根据查询需求和数据特点合理地添加索引。

可以通过创建单列索引、复合索引或者位图索引等方式来优化SQL查询。

2. 确保SQL语句逻辑正确SQL语句的逻辑错误可能会导致低效查询。

因此，在编写SQL语句前，需要仔细分析查询条件，确保逻辑正确性。

3. 使用连接替代子查询在一些场景下，使用连接（JOIN）操作可以替代子查询，从而减少查询的复杂度。

连接操作能够将多个数据集合合并为一个结果集，避免多次查询和表的扫描操作。

4. 避免使用通配符查询通配符查询（如LIKE '%value%'）在一些情况下可能导致全表扫描，性能低下。

尽量使用前缀匹配（LIKE 'value%'）或者使用全文索引进行模糊查询。

5. 注意选择合适的数据类型选择合适的数据类型有助于提高SQL查询的效率。

对于整型数据，尽量使用小范围的数据类型，如TINYINT、SMALLINT等。

对于字符串数据，使用CHAR字段而不是VARCHAR，可以避免存储长度不一致带来的性能问题。

6. 优化查询计划查询计划是数据库在执行SQL查询时生成的执行计划。

通过使用EXPLAIN PLAN命令或者查询计划工具，可以分析查询计划，找出性能瓶颈所在，并对其进行优化。

7. 减少磁盘IO磁盘IO是影响查询性能的重要因素之一。

可以通过增加内存缓存区（如SGA）、使用高速磁盘（如SSD）、使用合适的文件系统（如ASM）等方式来减少磁盘IO。

8. 分区表对于大数据量的表，可以考虑使用分区表进行查询优化。

分区表可以将数据按照某个规则分散到不同的存储区域，从而减少查询范围和加速查询。

9. 批量操作尽量使用批量操作而不是逐条操作，可以减少数据库的事务处理开销，提高SQL执行效率。

可以使用INSERT INTO SELECT、UPDATE、DELETE等批量操作语句来实现。

基于Oracle的SQL优化-SQL优化方法论幻灯片

基于Oracle的SQL优化-SQL优化方法论幻灯片

• create index idx_t1 on t1(object_id);
16
17
用合适的函数索引来避免看似无法避免的全表扫描
• drop index idx_t1; • create index idx_t1 on t1(object_name); • select object_name,object_id from t1 where object_name
6
Reduce the Workload
• 方法1所涉及到的这两种优化手段在Oracle数据库中能否奏效 以及效果的好坏与否很大程度上取决于对CBO和执行计划的理 解程度，对CBO和执行计划理解的越深，这两种优化手段的应 用就会越纯熟，效果就会越好。这也是这本书所要提出的 Oracle数据库里SQL优化方法论的第一点：Oracle里SQL优化 的本质是基于对CBO和执行计划的深刻理解
5
Reduce the Workload
我们在“第四章 Oracle里的查询转换”中曾经提到过一个案例， 在
那个例子里，我们在不更改原有业务逻辑的情况下通过将目标 SQL由原语句： • select pubamnt from v_bc_lcgrppol where grppolno in
(select grppolno from v_bc_lcpol where polno = '9022000000000388'); 改写为： • select pubamnt from v_bc_lcgrppol a,(select distinct grppolno grppolno from v_bc_lcpol where polno = '9022000000000388') b where a.grppolno=b.grppolno; 后就实现了将原SQL的逻辑读从200万降低到了6，其执行时间也 从6秒降低到了毫秒级这样一个极好的优化效果

ORACLE数据库SQL优化与提高

ORACLE数据库SQL优化与提高查询设计建议：方针●决定索引取舍：对不常用的索引删除以提高更新性能●保持优化器最新的统计信息●把复杂的查询变成多个简单的查询的联合●尽量避免使用嵌套子查询●如果可能，将自链接改成原始表与临时表副本的链接●为一系列查询创建临时表，性能好于视图，但临时表不会自动更新●将多个更新命令组合成一个，以减少查询Oracle 的优化器有两种优化方式基于规则的优化方式（rule-based optimization,简称RBO）:优化器在分析SQL语句时，所遵循的是ORACEL内部预定的一些规则。

比如我们常见的，当一个where子句中有一列索引时去走索引。

基于代价的优化方式（Cost-Based Optimization,简称CBO）：它是看语句的代价（Cost），这里的代价主要指CPU和内存。

优化器在判断是否用这种方式时，主要参照的是表和索引的统计信息。

统计信息给出表的大小，多少行、每行的长度信息等信息。

这些统计信息开始在库内是没有的，是做analyze之后才出现的，很多时候过期的统计信息会使优化器作出一个错误的执行计划，所以需要及时更新这些信息。

注意：走索引不一定就是优的，比如一个表只有两行数据，一次IO就可以完成全表的检索，而此时走索引时则需要两次IO,这时全表扫描（full table scan）是最好的优化程序模式Optimizer_mode = {choose|rule|first_rows|all_rows}缺省值为CHOOSE,如果统计对至少有一个被设计的表是可用的，则优化程序使用基于成本的模式（ALL_ROWS），否则使用给予规则的优化。

FIRST_ROWS 尽量减小立即相应时间，可能以占用总相应时间为代价。

ALL_ROWS 尽量减少总相应时间.会话级别：ALTER SESSION SET OPTIMIZER_MODE = V ALUEAlter session set optimizer_mode = {choose|rule|first_rows|all_rows}语句级别：SELECT /*+ FIRST_ROWS*/ * FROM SCOTT.EMP;Rule：基于规则的方式，忽略CBO和统计数据且完全基于数据字典信息生成执行计划。

ORACLE+SQL效率优化

SELECT DEPT_NO, DEPT_NAME FROM DEPT D WHERE EXISTS (SELECT 'X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO) 低效: SELECT DISTINCT DEPT_NO, DEPT_NAME FROM DEPT D, EMP E WHERE D.DEPT_NO = E.DEPT_NO
（19）SQL 语句用大写的，因为 ORACLE 总是先解析 SQL 语句，把小写的字母转换成大写的再执行。
（20）在 JAVA 代码中尽量少用连接符’＋’连接字符串！(注：JAVA 中的 String 类为 final)
（21）避免在索引列上使用 NOT 通常，我们要避免在索引列上使用 NOT，NOT 会产生在索引列上使用函数相同的影响。当 ORACLE 遇到
SELECT * FROM EMP WHERE EMPNO > 0 AND EXISTS (SELECT 'X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = 'MELB')
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 低效： SELECT * FROM EMP WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'MELB')
（2）WHERE 子句中的连接顺序 ORACLE 采用自下而上的顺序解析 WHERE 子句，根据这个原理,表之间的连接必须写在其他 WHERE 条件之

Oracle之SQL语句性能优化（34条优化方法）

Oracle之SQL语句性能优化（34条优化⽅法）好多同学对sql的优化好像是知道的甚少，最近总结了以下34条仅供参考。

（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)：ORACLE的解析器按照从右到左的顺序处理FROM⼦句中的表名，FROM⼦句中写在最后的表(基础表 driving table)将被最先处理，在FROM⼦句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。

如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引⽤的表.（2） WHERE⼦句中的连接顺序．：ORACLE采⽤⾃下⽽上的顺序解析WHERE⼦句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最⼤数量记录的条件必须写在WHERE⼦句的末尾.（3）SELECT⼦句中避免使⽤ ‘ * ‘：ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个⼯作是通过查询数据字典完成的, 这意味着将耗费更多的时间（4）减少访问数据库的次数：ORACLE在内部执⾏了许多⼯作: 解析SQL语句, 估算索引的利⽤率, 绑定变量 , 读数据块等；（5）在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200（6）使⽤DECODE函数来减少处理时间：使⽤DECODE函数可以避免重复扫描相同记录或重复连接相同的表.（7）整合简单,⽆关联的数据库访问：如果你有⼏个简单的数据库查询语句,你可以把它们整合到⼀个查询中(即使它们之间没有关系)（8）删除重复记录：最⾼效的删除重复记录⽅法 ( 因为使⽤了ROWID)例⼦：DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)FROM EMP X WHERE X.EMP_NO = E.EMP_NO);（9）⽤TRUNCATE替代DELETE：当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) ⽤来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执⾏删除命令之前的状况) ⽽当运⽤TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运⾏后,数据不能被恢复.因此很少的资源被调⽤,执⾏时间也会很短. (译者按: TRUNCATE只在删除全表适⽤,TRUNCATE是DDL不是DML)（10）尽量多使⽤COMMIT：只要有可能,在程序中尽量多使⽤COMMIT, 这样程序的性能得到提⾼,需求也会因为COMMIT所释放的资源⽽减少:COMMIT所释放的资源:a. 回滚段上⽤于恢复数据的信息.b. 被程序语句获得的锁c. redo log buffer 中的空间d. ORACLE为管理上述3种资源中的内部花费（11）⽤Where⼦句替换HAVING⼦句：避免使⽤HAVING⼦句, HAVING 只会在检索出所有记录之后才对结果集进⾏过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE⼦句限制记录的数⽬,那就能减少这⽅⾯的开销. (⾮oracle中)on、where、having这三个都可以加条件的⼦句中，on是最先执⾏，where次之，having最后，因为on是先把不符合条件的记录过滤后才进⾏统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，where也应该⽐having快点的，因为它过滤数据后才进⾏sum，在两个表联接时才⽤on的，所以在⼀个表的时候，就剩下where跟having⽐较了。

oracle数据库sql的优化总结

oracle数据库sql的优化总结⼀：使⽤where少使⽤having;⼆：查两张以上表时，把记录少的放在右边；四：有where⼦查询时，⼦查询放在最前；五：select语句中尽量避免使⽤*（执⾏时会把*依次转换为列名）；六：尽量多的使⽤commit；七：Decode可以避免重复扫描相同的记录或重复连接相同的表；九：连接多个表时，使⽤别名并把别名前缀于每个字段上；⼗：⽤exists代替in⼗⼀：not exists代替 not in（not in 字句将执⾏⼀个内部的排序和合并，任何情况下，not in是最低效的，⼦查询中全表扫描了。

为了避免使⽤not in，可以改写成outer joins或not exists）;⼗⼆：表连接⽐exists更⾼效；例：低：⾼：select distinct dept_no, dept_name select dept_no, dept_namefrom dept d, emp e from dept dwhere 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列的值。

基于Oracle数据库的几种常见SQL优化策略研究

INFORMATION TECHNOLOGY 信息化建设摘要：随着我国进入21世纪信息时代，对数据库提出了更高的要求。

Oracle数据库是当今使用较多的数据库之一，特别是数据容量逐渐变大的趋势下，进行SQL优化显得尤为关键。

基于此，论文通过阐述SQL语句优化的必要性，对Oracle数据库的几种常见SQL优化策略展开了详细的探讨。

关键词：Oracle数据库；SQL优化；优化策略一、SQL语句优化的必要性SQL语句是数据分析和应用中最常用的方式，大多数APP软件的开发和运行大多数会执行SQL语句，并且数据库中一大半资源为SQL语句，因此进行SQL语句的优化可以极大的提高数据库系统运行的效率和性能。

同时，SQL语句简单易上手，不需要投入太多的优化成本，但是真正精通SQL 语句并不轻松，SQL语句没有固定的写法，不同的SQL语句写法对应的数据分析和处理性能有所不同，还直接决定着数据库的结构和效率库运行的效率，所以进行SQL语句优化能够满足高容量的数据库需求，使得Oracle数据库达到低成本的优化原则，并且可以减少Oracle数据库之间的交互次数，实现了漏斗原则。

二、Oracle数据库的几种常见SQL优化策略（一）使用优化软件完成SQL的优化。

人工智能技术的兴起和渗入到Oracle数据库的优化方案中，使数据库的优化技术得到质的改善，在SQL语句的优化中使用人工智能技术，实现SQL优化的智能和高效。

使用人工智能优化软件的实质在于自动编写SQL语句，并从所有SQL语句中筛选出最有效的语句，同时智能SQL优化也是未来发展趋势[1]。

商业领域需要数据更加稳定可靠，LECCO SQL Expert可以轻松的编译出专家级别的SQL语句，从而减少SQL优化的难度系数，因此该优化软件非常适用于商业数据库的优化，它只需进行数据的测试分析就能选出性能最好的SQL语句，满足不同用户的需求。

（二）选取更多WHERE语句替代HAVING语句。

oracle优化-SQL优化

Oracle优化—SQL优化1、数据库、数据表、数据表数据库、数据表、数据表I/O优化原则数据库规划原则●最大可重用化⏹数据库重大问题时，通过备份和恢复机制最大程度上恢复数据●最小磁盘争用⏹数据库文件平均分布在不同的磁盘上，避免多用户访问时争用同一磁盘●各种数据的合理分布⏹将数据库中的各种数据按特性（如基表和变化表、大数据和常规数据）存储在不同的文件中。

●数据表规划原则⏹数据定义精确化◆满足要求的情况下，选择占用资源最少的数据类型以提高DBMS的I/O性能⏹表的抽象化◆通过将具有共性的表合并，将其特性以标识字段表示⏹表的范式化◆设计表时，满足1NF（原子性）、2NF（键相关性）、3NF（无函数相关性），保证表的结构无冗余●数据表I/O优化原则⏹变化表和基表的分离◆将变化表（递增性很强，如单据表、销售记录表）和基表（很少变化，如学生资源、班组资料等）分开存放到不同的数据文件中⏹大数据和常规数据的分离◆将表的long、lob等大数据字段和其他常规类型字段分开存放，保证常规数据的查询高效性⏹索引数据和表数据的分离◆将索引和表分离，减少磁盘争用；并提高索引的查询效率2、选用适合的ORACLE优化器优化器RBO/CBOOracle的优化器有两种优化方式,即基于规则的优化方式(Rule-Based Optimization，简称为RBO)和基于代价的优化方式(Cost-Based Optimization，简称为CBO)，在Oracle8及以后的版本,Oracle强列推荐用CBO的方式。

RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。

比如我们常见的，当一个where子句中的一列有索引时去走索引。

CBO方式：它是看语句的代价(Cost),这里的代价主要指Cpu和内存。

优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。

统计信息给出表的大小、有少行、每行的长度等信息。

这些统计信息起初在库内是没有的，是做analyze命令后才出现的，很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些应及时更新这些信息。

  1. 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
  2. 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
  3. 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

View Merging: Example
• View_merging_example_1.txt
View Merging Restrictions
For a view to be considered for merging, it must contain none of the following: • Set operators (UNION, UNION ALL, INTERSECT, MINUS) • A CONNECT BY clause • A ROWNUM pseudocolumn
Subqueries That Can Be Unnested
Subqueries using the following conditional expressions can be unnested, if certain restrictions are met: • ANY (including IN) • ALL (including NOT IN) • EXISTS • NOT EXISTS • Single row (=, <, >, <=, >=)
基于Oracle的SQL优化
崔华（dbsnake)
About Me
• 中航信工程师 • Oracle ACE • ACOUG成员
基于Oracle的SQL优化
• • • • • • • • 第一章 第二章 第三章 第四章 第五章 第六章 第七章 第八章 Oracle里的优化器 Oracle里的Cursor和绑定变量 Oracle里的查询转换 Oracle里的统计信息 Oracle里的执行计划 Oracle里的Hint Oracle里的并行 Oracle里SQL优化的方法论
View Merging: Example
• View_merging_example_2.txt
Pushing Predicates
• The optimizer can transform a query block that accesses a nonmergeable view by pushing the query block's predicates inside the view's query or pulling the predicates from the view into the main query. • Pushing_predicates_example.txt • other_hints_example.txt
Lesson 5: Query Rewrite
• Describe the concept of subquery unnesting and identify its restrictions • Explain view merging • Describe how the optimizer evaluates IN-lists
Subquery Unnesting: Examples
• Subquery_unnesting_example.txt
View Merging
• View merging rewrites queries containing views so that only the base tables remain. • View merging is only performed when a correct result is guaranteed. • View merging is performed prior to query optimization.
Subquery Unnesting Notes
• the order of tables or join conditions does not specify the order of evaluation • the parentheses around a subquery do not imply that it should be evaluated first as a separate entity
IN-List Expansion
Converts IN-lists and OR expressions into a series of concatenated UNION ALL statements
IN-List and OR as a Filter
• In this case, the values in the IN-list are used to filter the result set after it has been retrieved. • In_list_and_or_example.txt
Thanks!
Subquery Unnesting
• The optimizer has two choices for subquery optimization: － Transform the subquery into an equivalent join and then optimize that join statement － Optimize the subquery as it is • If no equivalent join statement exists, then no transformation occurs. • Statements are equivalent if they return exactly the same rows under all circumstances.
IN-List and OR Evaluation
There are three methods available to the optimizer for handling IN-lists and OR expressions: • Use IN-list iterator functionality • Expand the list into a series of UNION ALL queries, one per expression • Apply the expressions as a filter • in_and_notin_example.txt
IN-List Iterator
• The IN-list iterator function compares each row in the row source with each value from the IN-list. • The column compared with the IN-list must be indexed to use the IN-list Iterator function. • Typically, the IN-list iterator is more efficient than OR expansion. • There is no hint to force the IN-list iterator
Subquery Unnesting Example
When Is Subquery Unnesting Performed?
• Subquery unnesting is performed in the query rewrite phase • Because subquery unnesting occurs prior to query optimization, it is optimizer independent. • In Oracle9i no unnesting operations are costed.ห้องสมุดไป่ตู้• In Oracle 10g/above unnesting operations are costed
A Comprehensive Example of IN-List
• XX9客户性能优化案例.doc － 顶尖高手也会犯错 a) in后面如果不是常量则无法应用IN-List Iterator和or expansion b) view中带union all则无法view merge c) 所以根本原因不在于view无法merge，而是在于in的filter执行 计划导致的谓词无法向上推入 d) 改掉sql中的in实现了谓词的推入
Nonmergeable View Examples: ROWNUM Reference
Complex View Merging
• Enhancement that allows views containing GROUP BY or DISTINCT to be merged • Complex view merging can also be used to merge the queries produced by subquery unnesting.