第13章SQL语句优化.pptVIP

第13章 SQL语句优化 本章要点： 掌握SELECT子句的优化。 掌握WHERE子句的优化。 了解COMMIT语句的作用。 熟练掌握使用表连接替代多个查询。 了解EXISTS操作符的使用。 理解使用“=”替代“”。 熟练掌握表的连接优化。 掌握索引的优化。 13.1 一般的SQL优化技巧 由于Oracle可以存储更多更复杂的数据，这就使得数据的查询效率显得更为重要，低效率的查询给实际应用带来的麻烦是不可估量的。 对于DELETE语句、SELECT子句、FROM子句和WHERE子句等都可以进行优化，本节将具体介绍不同情况下的优化技巧。 13.1.1 SELECT语句中避免使用“*” 在SELECT子句查询表的所有列时，可以使用动态SQL列引用“*”，用来表示表中所有的列。使用“*”替代所有的列，可以降低编写SQL语句的难度，减少SQL语句的复杂性，但是却降低了SQL语句执行的效率。 Oracle需要执行如下步骤： 在共享池中搜索SQL语句是否已经存在。 验证SQL语句的语法是否准确。 执行数据字典来验证表和列的定义。 获取对象的分析锁，以便在语句的分析过程中对象的定义不会改变。 检查用户是否具有相应的操作权限。 确定语句的最佳执行计划。 将语句和执行方案保存到共享的SQL区。 13.1.1 SELECT语句中避免使用“*” 【例13.1】首先使用SET TIMING ON语句显示执行时间。然后检索scott用户的emp表，使用“*”来替代所有的列名，执行语句和执行时间如下： SQL SET TIMING ON SQL SELECT * FROM scott.emp; 已用时间: 00: 00: 00.37 在SELECT子句中不使用“*”，而使用具体的列名，执行语句和执行时间如下： SQL SELECT empno , ename , job , mgr , hiredate , sal , comm , deptno 2 FROM scott.emp ; 已用时间: 00: 00: 00.26 13.1.2 使用WHERE子句替代HAVING子句 在SELECT语句中，使用WHERE子句过滤行，使用HAVING子句过滤分组，也就是在行分组之后才执行过滤。 【例13.2】对scott用户的emp表进行操作，根据deptno列进行分组，并使用HAVING子句过滤deptno列的值为10的记录信息。具体如下： SELECT deptno , avg(sal) FROM scott.emp GROUP BY deptno HAVING deptno 10; SELECT deptno , avg(sal) FROM scott.emp WHERE deptno 10 GROUP BY deptno; 13.1.3 使用TRUNCATE替代DELETE 删除表中的数据可以使用DELETE语句，也可以使用TRUNCATE语句。 其中，使用DELETE语句删除表中所有的数据时，Oracle会对数据逐行删除，并且使用回滚段来记录删除操作，如果用户在没有使用COMMIT提交之前使用ROLLBACK命令进行回滚操作，则Oracle会将表中的数据恢复到删除之前的状态。 使用TRUNCATE语句删除表中的所有数据行时，Oracle不会在撤消表空间中记录删除操作，这就提高了语句的执行速度。而且这种删除是一次性的，也就是执行一次TRUNCATE语句，所有的数据行是在同一时间被删除。 如果确定要删除表中的所有行，建议使用TRUNCATE语句。 13.1.3 使用TRUNCATE替代DELETE 【例13.3】创建一个表test，向该表中添加10000行记录数据。然后使用DELETE语句执行删除操作，如下： CREATE TABLE test (sno number,sname varchar2(15)); BEGIN FOR I IN 1..10000 LOOP INSERT INTO test VALUES(I,JACK); END LOOP; END; SQL DELETE FROM test; 同样的条件下，使用TRUNCATE语句执行删除操作，如下： SQL TRUNCATE TABLE test; 13.1.4 在确保完整性的情况下多用COMMIT语句 当用户执行DML操作后，如果不使用COMMIT命令进行提交，则Oracle会在回滚段中记录DML操作，以便用户使用ROLLBACK命令对数据进行恢复。 Oracle实现这种数据回滚功能，需要花费相应的时间与空间资源。 在确保数据完整性的情况下，尽量及时地使用COMMIT命令对DML操作进行提交。 13.1.