oracle之常规SQL语句优化

前言

本文将介绍oracle中的常规sql语句的优化

优化建议

建议不用*来代替所有列名

select语句中可以用*来列出某个表的所有列名，但是这样的写法对oracle系统来说会存在解析的动态问题。也就是说使用星号来代替所有的列名的时候，oracle并不会明确知道自己要查找的列是哪些，需要进行解析之后才会知道此星号指的到底是哪些列。
建议在写select语句时，采用与访问表有关的实际列名。

用TRUNCATE代替DELECTE

当时用DELETE删除表中的数据行时，oracle会使用撤销表空间（UNDO_TABLESPACE）来存放恢复的信息。在这期间如果用户没有发出COMMIT语句，二十发出ROLLBACK语句，oracle系统会将数据恢复到删除之前的状态。
当用户使用TRUNCATE语句对表的数据进行删除时，系统不会将删除的数据写到回滚段中，当然快得多。
当希望对表中的所有行进行全部删除时，采用truncate命令更加高效。
一句话总结：delete语句可以回滚，所以要保留回滚段，要花费额外的资源，truncate操作不需要保留回滚段，所以性能较高。

在确保完整性的情况下多用COMMIT语句

在PL/SQL块中，经常将几个相互联系的DML语句写在一个BEGIN … END块中，建议在每个块的END之前使用COMMIT语句，这样就可以实现对象DML语句的及时提交，同时也释放事务所占用的资源。
那么COMMIT所释放的资源有哪些呢？
1）回滚段上用于恢复数据的信息。（提交之前需要将原信息进行保留，以便回滚操作，提交之后原信息便可释放）
2）报程序语句获得的锁（由于事务的ACID特性，开始事务的同时会获取锁，提交之后事务结束，锁就得到释放）
3）oracle为管理上述资源的内部花费

尽量减少表的查询次数

此场景在子查询中比较常见。
下面贴出两段代码进行比对：
第一段：

SELECT
	e.employee_name ,
	e.salary
FROM
	employees e
WHERE
	e.department_id IN (
	SELECT
		d.department_id
	FROM
		departments d
	WHERE
		d.location = 'SALES')
	OR e.department_id IN (
	SELECT
		d.department_id
	FROM
		departments d
	WHERE
		d.location = 'RESEARCH');

第二段：

SELECT
	e.employee_name ,
	e.salary
FROM
	employees e
WHERE
	e.department_id IN (
	SELECT
		d.department_id
	FROM
		departments d
	WHERE
		d.location = 'SALES'
		OR d.location = 'RESEARCH');

上述两段代码需求均是查询地点在SALES 和RESEARCH地区的部门的员工姓名和薪水
但第一段代码对departments 进行了两次查询，第二段代码对departments 表进行了一次查询，所以第二段代码更为高效。

正确使用【not】in和【not】exists语句

in和exists的使用场景是在子查询中，加入子查询之设计到两个表（外表和内表），语句合适为

select 查询字段
from 外表
where 字段 in（内表）
【where exists （内表）】

**in的实现原理是：**首先查询的是内表，然后内表与外表进行连接，然后按照条件进行查询，所以内表比较小的时候in的速度较快。
**exists的实现原理：**首先查询的是外表，并对外表进行全表扫描，然后看外表中的记录有没有和内表的数据一样，匹配上就放入结果集中。

因此想要使效率高效需要满足的条件是：
使用in的情况下：内表为小表
使用exists情况下：内表为大表

注意where字句中条件的执行顺序

在Oracle数据库中where条件的执行顺序是：从右往左，从下往上
在Mysql数据库中where条件的执执行顺序是：从左往右，从上往下
为了提高效率，按照where字句中的执行效率，应该让“能剔除更多数据”的条件先执行，这样就能使后续的条件筛选更少的数据，从而减少无用功。
所以在Oracle数据库中，应该将能剔除更多数据的条件写在后面
而在Mysql数据库中，应该将能剔除更多数据的条件写在前面