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数据库中,应该将能剔除更多数据的条件写在前面