写出高性能SQL语句方法（上）
时间：2023-05-12 08:07:00
1.整合简单，访问无关数据库:
WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全资料表扫描。例如：
（高效）SELECT * FROM DEPT D WHERE D.SAL > 300000 / 12;
（低效）SELECT * FROM DEPT D WHERE D.SAL * 12 > 300000;
- 如果您有几个简单的数据库查询语句，您可以将其整合到查询中（即使它们之间没有关系）2。删除重复记录：
- 由于使用了最有效的删除重复记录方法(ROWID）例子：
- DELETE FROM EMP E WHERE E.ROWID > ( SELECT MIN( X.ROWID ) FROM EMP X WHERE X.EMP_NO = E.EMP_NO );
- 3、用TRUNCATE替代DELETE:
- 删除表中的记录时，通常情况下，回流滚段（ rollback segments ）用于存储可恢复的信息。如果你没有 COMMIT 事务， ORACLE 将数据恢复到删除前的状态（准确地说，它将恢复到执行删除命令前的状态） TRUNCATE 回滚段不再存储任何可恢复的信息。当命令运行时，数据无法恢复。因此，很少资源少，执行时间短。（注：TRUNCATE 全表只适用于删除，TRUNCATE 是DDL 不是 DML）
- 4.选择最有效的表名顺序(仅在基于规则的优化器中有效):
- ORACLE 根据从右到左的连续处理FROM句子中的表名，FROM最后一个表写在句子中(基楚数据表) driving table）先处理，在FROM如果句子中包含多个表，则必须选择记录条数最少的表作为基楚数据表。如果有3个以上的表连接查询，则需要选择交叉表（intersection table）交叉表作为基楚数据表，是指期货表引用的表。
- 5、WHERE子句中的连接顺序 . :
- ORACLE 采用自下而上的顺序分析 WHERE 根据这一原理，表之间的连接必须写在其他方面 WHERE 可以过滤掉最大记录的条件必须写在条件之前 WHERE 句子的末尾。
- 6、SELECT 避免使用避免使用 * ’：
- ORACLE在这个过程中，将‘ * 这项工作是通过查询数据字典依次转换为所有列名来完成的，这意味着将花费更多的时间。
- 7、减少访问资料库的次数：
- ORACLE内部执行了许多工作：分析SQL语句，估算索引的利用率，绑定变量，读资料块等。
- 8、在SQL*Plus，SQL*Forms 和Pro*C中重新设置 ARRAYSIZE 参数可增加每个数据库访问的检索数量，建议值为200.
- 9、使用DECODE降低处理时间的函数：
- 使用DECODE函数可以避免重复扫描相同的记录或重复连接相同的表。
- 10、用WHERE 子句替换HAVING子句：
- 避免使用HAVING子句，HAVING结果集只有在检索出所有记录后才会过度考虑。如果能通过排序、统计等操作进行处理WHERE限制记录的数量可以减少这方面的费用。（非oracle中）on 、where 、having这三个子句都可以添加条件，on是第一执行，where次之，having最后，因为on统计前先过滤不合格记录，可减少中间运算要处理的数据，按理说应该是最快的；where也应该比having快点的，因为它是在过滤数据之前进行的sum，在连接两个表时使用on是的，所以在一张桌子上，只剩下where跟having比较了。在意表查询统计的情况下，如果要过滤的条件不涉及要计算的栏的，只是where可以使用rushmore技术，而having就速度而言，后者应该很慢。如果涉及到计算栏，则表示该栏的值在计算前不确定。ORACLE工作流程，where工作时间在计算前完成，having计算后才起作用，所以在这种情况下，两者的结果会有所不同。联接查询时，on比where早起。联接查询时，on比where更早起作用。系统首先根据第各个表之间的联结条件，把多个表合成一个临时表后，再由where过滤，然后计算，然后计算having进行过滤。由此可见，要想在过滤条件中发挥正确的作用，首先要了解这种条件何时发挥作用，然后再决定放在那里。
- 11对表的查询：
- 包含子查询SQL在句子中，要特别注意减少对表的查询。
- SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = (SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 666 );
- 12.通过内建函数提高SQL效率：
- 复杂的SQL句子往往牺牲执行效率。在实际工作中，掌握使用函数解决问题的方法是非常有意义的。
- 13.使用表的别名（Alias）：
- 当在SQL在句子中连接多个表时，请使用表的别名并将别名首码放在每个表中Colmn上，这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。
- 14、用EXISTS替代IN、用NOT EXISTS替代NOT IN：
- 在许多基于基楚资料表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS（或NOT EXISTS）通常将提高查询的效率。在子查询中NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的（因为它对子查询中的表执行了一个全表遍历）。为了避免使用NOT IN，我们可以把它改写成外连接（Outer Joins）或NOT EXISTS。
- 例如：
- （高效）SELECT * FROM EMP E WHERE E.EMPNO > 0 AND EXISTS( SELECT ‘X’ FROM DEPT D WHERE D.EDPTNO = E.DEPTNO AND D.LOC = ‘MELB’ );
- （低效）SELECT * FROM EMP E WHERE E.EMPNO > 0 AND E.DEPTNO IN( SELECT D.DEPTNO FROM DEPT D WHERE LOC = ’MELB’ );
- 15、识别‘低效’执行的SQL语句：
- 虽然目前各种关于SQL优化的图形化工具层出不穷，但是写出自己的SQL工具来解决问题始终是一个最好的方法：
- SELECT EXECUTIONS,DISK_READS,BUFFER_GETS,
- ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
- ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
- SQL_TEXT FROM V$SQLAREA
- WHERE EXECUTIONS > 0
- AND BUFFER_GETS > 0
- AND (BUFFER_GETS-DISK_READS)/BUFFER_BETS < 0.8
- ORDER BY 4 DESC;
- 16、用索引提高效率：
-
索引是表的一个概念部分，用来提高检索资料的效率，ORACLE使用了一个复杂的自平衡B-tree结构。通过索引查询资料比全资料表扫描要快。当ORACLE找出执行Select和Update语句的最佳路径时，ORACLE优化器将使用索引。同样在联结多个表时使用索引也可以提高效率。另一个使用索引的好处是，它提供了主键（primary key）的唯一性验证。那些LONG或LONG RAW资料类型，你可以索引几乎所有的列。通常，在大型表中使用索引特别有效。当然，你也会发现，在扫描小表时，使用索引同样能提高效率。跃然使用索引能等到查询效率的提高，但是我们也必须注意到它的代价。索引需要空间来存储，也需要定期维护，每当有记录在表中培养或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT,DELETE,UPDATE将主此多付出4至5次的磁片I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。定期的重构索引是有必要的：
ALTER INDEX
REBUILD -
17、用EXISTS 替换DISTINCT:
-
当提交一个包含一对多表资讯（比如部门表和雇员表）的查询时，避免在SELECT子句中使用DISTINCT。一般可以考虑用EXIST替换，EXISTS全查询更主迅速，因为RDBMS核心模组将在子查询的条件一旦满足后，立刻返回结果。例如：
（高效）SELECT D.DEPT_NO,D.DEPT_NAME FROM DEPT D WHERE EXISTS( SELECT 1 FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO );
（低效）SELECT DISTINCT D.EDPT_NO,D.DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO=E.DEPT_NO;
-
18、在java代码中尽量活用转接了“+”连接字串。
-
19、避免在索引列上使用NOT，NOT会对索引列产生影响，当ORACLE”遇到”NOT，就会停止使用索引转而执行全资料表扫描。
-
20、避免在索引列上使用计算：
-
WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全资料表扫描。例如：
（高效）SELECT * FROM DEPT D WHERE D.SAL > 300000 / 12;
（低效）SELECT * FROM DEPT D WHERE D.SAL * 12 > 300000;
