Oracle SQL性能优化

2021-11-29 21:00 575阅读 0赞

1、选用适合的Oracle优化器

RULE（基于规则） COST（基于成本） CHOOSE（选择性）

2、增加索引

重构索引：

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

避免在索引列上使用计算：

低效：  
SELECT …  
FROM DEPT  
WHERE SAL \* 12 > 25000;  
高效：  
SELECT …  
FROM DEPT  
WHERE SAL > 25000/12;  
用 >= 替代 >：

低效：  
SELECT \*  
FROM EMP  
WHERE DEPTNO >3  
高效：  
SELECT \*  
FROM EMP  
WHERE DEPTNO >=4  
3、 共享 SQL 语句

Oracle提供对执行过的SQL语句进行高速缓冲的机制。被解析过并且确定了执行路径的SQL语句存放在SGA的共享池中。  
Oracle执行一个SQL语句之前每次先从SGA共享池中查找是否有缓冲的SQL语句，如果有则直接执行该SQL语句。  
可以通过适当调整SGA共享池大小来达到提高Oracle执行性能的目的。  
4、选择最有效率的表名顺序

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表 driving table)将被最先处理。  
当ORACLE处理多个表时，会运用排序及合并的方式连接它们。首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行派序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。  
举例：

表 TAB1 有 9999 条记录

表 TAB2 有 1 条记录

PS：就是把数据多的作为基础表；交叉表作为基础表

/\*选择TAB1作为基础表 (最好的方法)\*/  
select count(\*) from tab1,tab2 执行时间0.96秒  
  
/\*选择TAB2作为基础表 (不佳的方法)\*/  
select count(\*) from tab2,tab1 执行时间26.09秒  
如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表。

SELECT \* FROM LOCATION L, CATEGORY C, EMP E  
WHERE E.EMP\_NO BETWEEN 1000 AND 2000  
AND E.CAT\_NO = C.CAT\_NO  
AND E.LOCN = L.LOCN  
将比下列SQL更有效率

SELECT \* FROM EMP E, LOCATION L, CATEGORY C  
WHERE E.CAT\_NO = C.CAT\_NO  
AND E.LOCN = L.LOCN  
AND E.EMP\_NO BETWEEN 1000 AND 2000  
5、Where子句中的连接顺序

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

PS：能过滤数据量的条件往后边放

/\*低效,执行时间156.3秒\*/  
SELECT …  
FROM EMP E  
WHERE SAL > 50000  
AND JOB = ‘MANAGER’  
AND 25 < (SELECT COUNT(\*) FROM EMP  
WHERE MGR = E.EMPNO)  
/\*高效,执行时间10.6秒\*/  
SELECT …  
FROM EMP E  
WHERE 25 < (SELECT COUNT(http://www.my516.com) FROM EMP  
WHERE MGR=E.EMPNO)  
AND SAL > 50000  
AND JOB = ‘MANAGER’  
6、SELECT子句中避免使用 \*

7、使用Truncate而非Delete

Delete表中记录的时候，Oracle会在Rollback段中保存删除信息以备恢复。Truncate删除表中记录的时候不保存删除信息，不能恢复。因此Truncate删除记录比Delete快，而且占用资源少。  
删除表中记录的时候，如果不需要恢复的情况之下应该尽量使用Truncate而不是Delete。  
Truncate仅适用于删除全表的记录。  
8、用Where子句替换Having子句

避免使用HAVING子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序、总计等操作。 如果能通过WHERE子句限制记录的数目，就能减少这方面的开销。

9、减少对表的查询操作

在含有子查询的SQL语句中，要注意减少对表的查询操作。

低效：  
SELECT TAB\_NAME FROM TABLES  
WHERE TAB\_NAME =（SELECT TAB\_NAME  
FROM TAB\_COLUMNS  
WHERE VERSION = 604）  
AND DB\_VER =（SELECT DB\_VER  
FROM TAB\_COLUMNS  
WHERE VERSION = 604）  
高效：  
SELECT TAB\_NAME FROM TABLES  
WHERE （TAB\_NAME，DB\_VER）=  
（SELECT TAB\_NAME，DB\_VER  
FROM TAB\_COLUMNS  
WHERE VERSION = 604）  
10、 使用表的别名（Alias）

当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

11、用EXISTS替代IN

12、用NOT EXISTS替代NOT IN

13、用IN替换OR

在实际项目中，尽量少用 IN ，能用循环解决的别用 IN 。循环比 IN 的效率高很多很多。

14、 使用UNION ALL替代UNION

当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并，然后在输出最终结果前进行排序。如果用UNION ALL替代UNION，这样排序就不是必要了，效率就会因此得到提高。

由于UNION ALL的结果没有经过排序，而且不过滤重复的记录，因此是否进行替换需要根据业务需求而定。

15、几种不能使用索引的WHERE子句

(1)、‘!=’ 将不使用索引

(2)、使用了数学函数

低效：WHERE AMOUNT + 3000 >5000；

高效：WHERE AMOUNT >2000；

(3)、相同的索引列不能互相比较

16、避免使用耗费资源的操作

带有DISTINCT，UNION，MINUS，INTERSECT，ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序（SORT）功能。DISTINCT需要一次排序操作，而其他的至少需要执行两次排序。  
通常，带有UNION，MINUS，INTERSECT的SQL语句都可以用其他方式重写。  
17. 使用显示游标(CURSORS)

使用隐式的游标，将会执行两次操作。第一次检索记录，第二次检查TOO MANY ROWS 这个exception。而显式游标不执行第二次操作。

18. 分离表和索引

总是将你的表和索引建立在不同的表空间内（TABLESPACES）。  
决不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里。  
确保数据表空间和索引表空间置于不同的硬盘上。  
\---------------------

转载于:https://www.cnblogs.com/ly570/p/10987477.html