oracle SQL优化规则有哪些

51次阅读

共计 2742 个字符，预计需要花费 7 分钟才能阅读完成。

这篇文章主要讲解了“oracle SQL 优化规则有哪些”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“oracle SQL 优化规则有哪些”吧！

1 选择最有效率的表名顺序：

1. 把记录少的表放在 from 子句的最后面一个表.

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

原因：ORACLE 的解析器按照从右到左的顺序处理 FROM 子句中的表名, 因此 FROM 子句中写在最后的表 (基础表 driving table) 将被最先处理. 在 FROM 子句中包含多个表的情况下, 你必须选择记录条数最少的表作为基础表. 当 ORACLE 处理多个表时, 会运用排序及合并的方式连接它们. 首先, 扫描第一个表 (FROM 子句中最后的那个表) 并对记录进行排序, 然后扫描第二个表(FROM 子句中最后第二个表), 最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.

2 WHERE 子句中的连接顺序：

ORACLE 采用自右向左的顺序解析 WHERE 子句,, 那些可以过滤掉最大数量记录的条件必须写在 WHERE 子句的末尾.

3. SELECT 子句中避免使用 lsquo;* lsquo;

ORACLE 在解析的过程中, 需要通过查询数据字典将 * 依次转换成所有的列名.

4. 使用表的别名(Alias)

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

(Column 歧义指的是由于 SQL 中不同的表具有相同的 Column 名, 当 SQL 语句中出现这个 Column 时,SQL 解析器无法判断这个 Column 的归属)

5. 减少访问的次数:

当执行每条 SQL 语句时, ORACLE 在内部执行了许多工作: 解析 SQL 语句, 估算索引的利用率, 绑定变量, 读数据块等等. 由此可见, 减少访问数据库的次数, 就能实际上减少 ORACLE 的工作量.

6. (可能的话)用 TRUNCATE 替代 DELETE.

当删除表中的记录时, 在通常情况下, 回滚段(rollback segments) 用来存放可以被恢复的信息. 如果你没有 COMMIT 事务,ORACLE 会将数据恢复到执行删除命令之前的状况.

而当运用 TRUNCATE 时, 回滚段不再存放任何可被恢复的信息. 当命令运行后, 数据不能被恢复. 因此很少的资源被调用, 执行时间也会很短.

(TRUNCATE 只在删除全表里的记录时适用,TRUNCATE 是 DDL 不是 DML)

7. (可能的话)使用 COMMIT

只要有可能, 在程序中尽量多使用 COMMIT, 这样程序的性能得到提高, 需求也会因为 COMMIT 所释放的资源而减少:

COMMIT 所释放的资源:

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

b. 被程序语句获得的锁

c. redo log buffer 中的空间

d. ORACLE 为管理上述 3 种资源中的内部花费

8. (可能的话)用 Where 子句替换 HAVING 子句

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

9. (某些情况下)可以用 EXISTS 替代 IN . NOT EXISTS 替代 NOT IN

性能比较:

1.Select * from t1 where x in (select y from t2)

2.select * from t1 where

exists (select 1 from t2 where t2. y = t1.x)

当 t1 记录比较少,t2 比较大时适合用 exists(exists 大部分情况会利用到 index), 当子查询记录集很小时用 in 比较合适.

原因分析：

1.Select * from T1 where x in (select y from T2)

执行的过程相当于:

select * from t1, (select distinct y from t2) t3 where t1.x = t3.y;

2. select * from t1 where exists (select 1 from t2 where t2.y = t1.x)

执行的过程相当于:

for x in (select * from t1) loop

if (exists ( select 1 from t2 where t2.y = t1.x)

then

OUTPUT THE RECORD

end if

end loop

这样表 T1 要被完全扫描一遍.

所以可以得出结论: 当 t1 记录比较少,t2 比较大时适合用 exists(exists 大部分情况会利用到 index), 当子查询记录集很小时用 in 比较合适.

10. 用表连接替换 EXISTS

改进第 9 打优化规则的例子.

11. 用 EXISTS 替换 DISTINCT

EXISTS 使查询更为迅速, 因为 RDBMS 核心模块在子查询的条件一旦满足后

立刻返回结果. DISTINCT 会先进行排序, 然后会根据排序后的顺序去除相同的行.

12. 使用显式的游标(CURSOR)

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

11- 例：

1.(低效)SELECT DISTINCT DEPT_NO,DEPT_NAME

FROM DEPT D,EMP E

WHERE D.DEPT_NO = E.DEPT_NO ;

2. 高效:

SELECT DEPT_NO,DEPT_NAME

FROM DEPT D

WHERE EXISTS (SELECT *

FROM EMP E

WHERE E.DEPT_NO = D.DEPT_NO);

bull;13. 用索引提高效率

通常, 通过索引查询数据比全表扫描要快. 当 ORACLE 找出执行查询和 Update 语句的最佳路径时, ORACLE 优化器将使用索引.

除了那些 LONG 或 LONG RAW 数据类型, 你可以索引几乎所有的列. 在大型表中使用索引特别有效.

虽然使用索引能得到查询效率的提高, 但是我们也必须注意到它的代价. 索引需要空间来存储, 也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的 INSERT , DELETE , UPDATE 将为此多付出 4 , 5 次的磁盘 I /O . 因为索引需要额外的存储空间和处理, 那些不必要的索引反而会使查询反应时间变慢.

定期的重构索引是有必要的.

ALTER INDEX INDEXNAME REBUILD TABLESPACENAME

感谢各位的阅读，以上就是“oracle SQL 优化规则有哪些”的内容了，经过本文的学习后，相信大家对 oracle SQL 优化规则有哪些这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！

正文完