如何优化SQL语句

ORACLE的解析器从右到左处理FROM子句中的表名，FROM子句写入。

最后一个表(基本表，驱动表)将首先被处理，FROM子句包含多个表。

在这种情况下，必须选择记录数最少的表作为基本表。如果有三个以上的表连接查询

，那么就需要选择交集表作为基本表，而交集表指的是

他的表所引用的表。

(where子句中的连接顺序。；

ORACLE以自下而上的顺序解析WHERE子句。根据这个原则，表之间的连接必须是

必须写在其他WHERE条件之前，那些能筛选出最大记录数的条件必须写在WHERE中。

子句的结尾。

(3)避免在select子句中使用' * ':

在解析的过程中，ORACLE会依次将' * '转换成所有的列名。这项工作是通过

查询数据字典，意味着要花更多的时间。

⑷减少访问数据库的次数:

ORACLE内部做了很多工作:解析SQL语句，估计索引的利用率，绑定变更。

数量、读取数据块等。；

(5)重置SQL*Plus、SQL*Forms和Pro*C中的ARRAYSIZE参数可以增加

每次数据库访问检索的数据量，建议值为200。

(6)使用解码功能减少处理时间:

使用DECODE函数可以避免重复扫描相同的记录或连接相同的表。

(7)简单的集成和无关的数据库访问:

如果您有几个简单的数据库查询语句，您可以将它们集成到一个查询中(即使

它们之间没有关系)

(8)删除重复记录:

删除重复记录的最有效方法示例(因为使用了ROWID):

从EMP E中删除WHERE E . ROWID & gt(选择最小值(X.ROWID)

FROM EMP X其中X . EMP _ NO = E . EMP _ NO)；

(9)用截断替换删除:

删除表中的记录时，一般情况下，使用回滚段来保存。

放可以恢复的信息。如果没有提交事务，ORACLE将在删除前恢复数据。

的状态(确切地说，是还原到执行delete命令之前的状态)，当使用TRUNCATE时，它返回。

滚动部分不再存储任何可以恢复的信息。命令运行时，数据无法恢复，所以很少见。

当资源被调用时，执行时间会很短。(译者按:TRUNCATE只删除整个表。

TRUNCATE是DDL，不是DML

(10)尽可能使用提交:

只要有可能，就在程序中尽可能多地使用COMMIT，这样程序的性能提高了，需求也

将因提交释放的资源而减少:

提交释放的资源:

A.用于恢复回滚段上的数据的信息。

B.程序语句获得的锁

C.重做日志缓冲区的空间

D.ORACLE管理上述三种资源中的内部费用。

将HAVING子句替换为Where子句:

避免使用HAVING子句。只有在检索了所有记录之后，才会对结果集执行HAVING。

过滤。这个过程需要排序和合计等操作。如果WHERE子句可以限制记录的数量，

那会降低这方面的成本。(不在甲骨文中)on，where，having都可以加。

在一个片段的子句中，on是第一个被执行的，其次是where，having是最后一个，因为on是第一个放不符合项的子句。

只有筛选出一条记录后才能进行统计，这样可以减少中间操作中要处理的数据，本来应该是

速度是最快的，并且应该比having快，因为它在求和之前过滤数据

当连接两个表时，使用on，因此当连接一个表时，只有where与having进行比较。

在这种单表查询统计的情况下，如果要过滤的条件不涉及要计算的字段，那么它们

结果是一样的，只是拉什莫尔技术可以用在速度上，而不能用在速度上

后者速度较慢。如果涉及计算字段，则表示该字段的值不在计算之前。

当然，按照上一篇文章写的工作流程，where的动作时间是在计算之前完成的，而

Having只有在计算后才起作用，所以在这种情况下，结果会有所不同。在多

当表联接查询时，on比where更早工作。首先，根据表格之间的连接情况，

将多个表组合成一个临时表后，按where筛选，然后计算，再按

必须过滤。因此，如果你想让过滤条件发挥正确的作用，你必须先理解这篇文章。

应该什么时候起作用，然后再决定放在哪里。

(12)减少对表的查询:

在带有子查询的SQL语句中，应该特别注意减少对表的查询。示例:

从表中选择TAB_NAME，其中(TAB_NAME，DB_VER) = ( SELECT

TAB_NAME，DB_VER FROM TAB_COLUMNS其中VERSION = 604)

(13)通过内部函数提高SQL效率。

复杂的SQL经常牺牲执行效率。能够掌握以上利用函数解决问题的方法。

在实际工作中很有意义。

(14)使用表格的别名:

当在SQL语句中连接多个表时，使用表的别名并将别名作为每列的前缀。

这样可以减少解析时间，减少列歧义导致的语法错误。

(15)将IN替换为EXISTS，将NOT IN替换为NOT EXISTS

在许多基于基本表的查询中，为了满足一个条件，通常需要连接另一个表。

那么，在这种情况下，使用EXISTS(或NOT EXISTS)通常会提高查询的效率。

在查询中，NOT IN子句将执行内部排序和合并。在这两种情况下，不在是

效率最低(因为它对子查询中的表执行全表遍历)。避免使用NOT IN。

我们可以将其重写为外部连接或不存在。

示例:

(高效)SELECT * FROM EMP(基本表)其中EMPNO >；0且存在(选择

“x”来自部门，其中部门编号=员工。DEPTNO和LOC = 'MELB ')

(低效)SELECT * FROM EMP(基本表)其中EMPNO >；0和DEPTNO IN(选择

部门编号来自部门位置= 'MELB ')

(16)识别“低效执行”SQL语句:

虽然目前各种关于SQL优化的图形化工具层出不穷，但是我们自己编写SQL工具。

解决问题总是最好的办法:

SELECT执行，DISK_READS，BUFFER_GETS，ROUND((BUFFER_GETS-

DISK_READS)/BUFFER_GETS，2) Hit_radio，ROUND(DISK_READS/EXECUTIONS，2)

每次运行的读取次数，

SQL _ TEXT FROM V $ SQLAREA WHERE EXECUTIONS & gt；0和BUFFER _ GETS & gt0和

(BUFFER _ GETS-DISK _ READS)/BUFFER _ GETS & lt；0.8阶由4 desc；

(17)利用索引提高效率；

索引是表的概念部分，用于提高检索数据的效率。ORACLE使用复杂的。

杂项自平衡B树结构。通常，通过索引查询数据比扫描整个表要快。当ORACLE寻找它时，

在寻找执行查询和更新语句的最佳路径时，ORACLE优化器将使用索引。

对多个表使用一个索引也可以提高效率。使用索引的另一个优点是它提供了一个主键。

(主键)的唯一性验证。那些LONG或者LONG RAW数据类型，你几乎可以索引。

所有列。通常，在大型表中使用索引特别有效。当然，你也会发现当扫描小

表，使用索引也可以提高效率。虽然使用索引可以提高查询效率，但是我们

我们还必须注意它的成本。索引需要空间来存储，并且需要定期维护。每当中有记录时

当添加或删除表或者修改索引列时，索引本身也会被修改。这意味着插入每条记录。

，DELETE，UPDATE会为此多付出4到5倍的磁盘I/O，因为索引需要额外的存储。

存储空间和处理，那些不必要的索引会降低查询响应时间。定期重建索引。

这是必要的。；

更改索引& ltINDEXNAME & gt重建& lt表空间名称& gt

(18)将DISTINCT替换为EXISTS:

当提交包含一对多信息表(如部门表和雇员表)的查询时，应避免使用。

在SELECT子句中使用DISTINCT。一般可以考虑用EXIST代替，这样查询速度更快。

速度，因为RDBMS核心模块会在子查询的条件满足时立即返回结果。示例:

(低效):从部门d、员工e中选择不同的部门编号、部门名称

其中，部门编号=部门编号(有效):从部门中选择部门编号、部门名称。

D .存在的位置(从EMP E中选择“X”，其中E . DEPT _ NO = D . DEPT _ NO)；

(19) sql语句大写；因为oracle总是先解析sql语句，在执行前将小写字母转换成大写字母。

(20)Java代码中尽量少用连接符“+”连接字符串！

21)避免在索引列上使用NOT。通常情况下，

我们应该避免使用NOT，NOT索引列，这与在索引列上使用函数具有相同的效果。当“ORACLE”遇到“NOT”时，它将停止使用索引，转而执行全表扫描。

(22)避免在索引列上使用计算。

在WHERE子句中，如果索引列是函数的一部分，优化器将使用全表扫描而不是索引。示例:效率低下:

SELECT…FROM DEPT WHERE SAL * 12 & gt；25000;高效:

SELECT … FROM部门SAL & gt25000/12;

(23)使用> =替换>

高效:

SELECT * FROM EMP WHERE DEPTNO & gt=4低效:

SELECT * FROM EMP WHERE DEPTNO & gt两者的区别在于，前者DBMS会直接跳转到DEPT等于4的第一条记录，而后者会先定位DEPTNO=3等于3的记录，然后向前扫描到DEPT大于3的第一条记录。

(24)用UNION替换OR(适用于索引列)

一般来说，用UNION替换WHERE子句中的OR会有很好的效果。对索引列使用或将导致全表扫描。请注意，上述规则仅对多个索引列有效。如果列没有索引，查询效率可能会降低，因为您没有选择或。在下面的示例中，LOC_ID和REGION都有索引。高效:select loc _ id，loc _ desc，Region from location where LOC _ ID = 10 union select loc _ id，loc _ desc，Region from location where Region = " Melbourne "低效:select LOC _ ID，LOC _ Desc，Region from location where LOC _ ID = 10 OR Region = " Melbourne "如果坚持使用OR，需要将记录最少的索引列写在最前面。

(25)将或改为。

这是一个简单易记的规则，但实际执行效果有待检验。在ORACLE8i下，两者的执行路径似乎是一样的。

低效:选择...从loc _ ID = 10 orloc _ ID = 20 orloc _ ID = 30有效选择的位置...从loc _ in (10，20，30)的位置；

(26)避免在索引列上使用IS NULL和IS NOT NULL。

避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引。对于单列索引，如果列包含空值，则记录将不存在于索引中。对于复合索引，如果每一列都为空，则记录也不会存在于索引中。如果至少有一列不为空，则记录将存在于索引中。例如，如果在表的A列和B列上建立了唯一索引，并且表中有一条记录A，如果B的值为(123，null)，则ORACLE不会接受下一条A和B的值相同(123，null)的记录(insert)。但是如果所有的索引列都是空的，ORACLE会认为整个键值都是空的，空不代表空。因此，可以插入1000条具有相同键值的记录。当然，因为索引列中不存在空值，所以在WHERE子句中比较索引列的空值会导致ORACLE禁用索引。

低效:(索引无效)where dept _ code不为空的部门；高效:(指标有效)select…from dept _ code >；=0;

(27)始终使用索引的第一列:

如果索引建立在多个列上，优化器将选择仅当where子句引用其第一个前导列时才使用索引。这也是一个简单而重要的规则。当只引用索引的第二列时，优化器使用全表扫描并忽略该索引。

(28)用UNION-ALL替换UNION(如果可能):

当SQL语句需要两个UNION的查询结果集时，这两个结果集将以UNION-ALL的方式进行合并，然后进行排序，最后输出结果。如果用UNION ALL代替UNION，排序就没必要了，效率也会相应提高。应该注意，UNION ALL将在两个结果集中重复输出相同的记录。因此，您应该从业务需求出发，分析使用UNION ALL的可行性。UNION将对结果集进行排序，这个操作将使用内存SORT_AREA_SIZE。对于这个内存的优化也是非常重要的。下面的SQL可以用来查询排序的消耗。

效率低下:

？

从借方交易中选择ACCT数量、余额金额

其中TRAN _日期= ' 365438+95年12月0日'工会选择ACCT _数量，余额_金额

从debt _ transactions，其中trans _ date = ' 31-dec-95 ' Efficient:SELECT ACCT _编号，余额_金额。

从借方交易，其中TRAN日期= ' 365438+95年12月0日' UNIONALLSELECT ACCT数量，余额金额

从借方交易，其中TRAN日期='31-DEC-95 '？

(29)将ORDER改为WHERE:

ORDER BY子句仅在两个严格的条件下使用索引。

ORDER BY中的所有列必须包含在同一个索引中，并保持索引中的顺序。

ORDER BY中的所有列都必须定义为非空。

WHERE子句中使用的索引和ORDER BY子句中使用的索引不能并置。

例如，表DEPT包含以下列:

DEPT_CODE主键不为空

DESC部门不为空

部门类型为空

低效:(不使用索引)按dept _ type从部门顺序中选择dept _ code高效:(使用索引)从dept _ type > 0的部门中选择dept _ code？

(30)避免改变索引列的类型；

在比较不同数据类型的数据时，ORACLE会自动对列执行简单的类型转换。

假设EMPNO是一个数字类型的索引列。select…from EMP where EMPNO = ' 123 '事实上，经过ORACLE类型转换后，该语句转换为:select…from EMP where EMPNO = to _ NUMBER(' 123 ')。幸运的是，索引列上没有发生类型转换，索引的用途也没有改变。现在，假设EMP_TYPE是字符类型的索引列。select…from EMP where EMP _ type = 123的语句被ORACLE转换为:select…from EMP where to _ number(EMP _ type)= 123。由于内部类型转换，此索引将不会更改。为了避免ORACLE对您的SQL进行隐式类型转换，最好是显式地表达类型转换。注意，在用数值比较字符时，ORACLE会优先将数值型转换为字符型？

31)要注意的WHERE子句:

某些SELECT语句中的WHERE子句不使用索引。这里有一些例子。在下面的例子中，(1)'！= '将不使用索引。请记住，索引只能告诉您表中存在什么，而不能告诉您表中不存在什么。(2)' || '是一个字符连接函数。与其他功能一样，索引是禁用的。(3)‘+’是一个数学函数。就像其他数学函数一样，索引被禁用。(4)相同的索引列不能相互比较，这将

(32) A .如果检索数据超过表中记录的30%，使用索引不会有明显的效率提升。

B.在某些情况下，使用索引可能比全表扫描慢，但两者的数量级相同。通常使用索引比全表扫描快几倍甚至几千倍！

(33)避免使用消耗资源的操作:

带有DISTINCT、UNION、MINUS、intersect和order by的SQL语句将启动SQL引擎来执行消耗资源的排序功能。Distinct需要执行一次排序操作，而其他distinct至少需要执行两次排序操作。通常，使用union、minus，INTERSECT的SQL语句可以用其他方式重写。如果你的数据库的SORT_AREA_SIZE分配的很好，UNION，MINUS，INTERSECT也可以考虑。毕竟它们可读性很强。

(34)通过以下方式优化组:

为了提高GROUP BY语句的效率，我们可以在GROUP BY之前过滤掉不必要的记录。以下两个查询返回相同的结果，但是第二个查询显然快得多。

效率低下:

选择职务，AVG(萨尔)

从职务=“总裁”或职务=“经理”有效的员工组职务:

选择职务，AVG(萨尔)

来自电磁脉冲

其中JOB =“总统”

或JOB = 'MANAGER '组作业

？

我们来看看甲骨文的执行流程。

分析SQL语句在Orcle中是如何工作的。

a、用户发送SQL请求并打开游标；

b .将SQL语句语法分析、执行计划、数据字典等信息存入内存* * *共享池；

c .将数据文件中的相关数据块读入数据缓冲区；

d .执行相应的操作，如果修改，先添加行级锁，确认后，将修改前后记录的内容存入重做日志缓冲区；

e .将结果返回给用户并关闭光标。

注意:SQL语句区分大小写。如果相同的语句区分大小写，

不一样，oracle需要执行两次分析，每句话后面都要跟“；”结束。