Oracle SQL优化技巧总结
优化
1.表名和字段名都尽量使用别名，可以减少oracle数据库解析表名和字段名的时间，且不要直接使用select *
2.⽤EXISTS替代IN、⽤NOT EXISTS替代 NOT IN。在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历).
语句1 SELECT dname, deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp); 语句2 SELECT dname, deptno FROM dept WHERE NOT EXISTS (SELECT deptno FROM emp WHERE dept.deptno = emp.deptno);
语句2要比语句1的执行性能好很多。因为1中对emp进行了full table scan（全表扫描）,这是很浪费时间的操作。而且1中没有用到emp的index， 因为没有where子句。而2中的语句对emp进行的是缩小范围的查询。
3.尽量用UNION-ALL 替换UNION (业务情况符合的话)，UNION-ALL可能会返回重复的数据，UNION会进行过滤，所以UNION的效率比较慢
4.利⽤>=替代>，因为>=可以直接定位到＝的位置，⽽>必须先定位位置，然后再查询下⼀个数据，耗时更久。
5.通配符%的使用，不要让’%'在前面，
WHERE子句中, 如果索引列所对应的值的第一个字符由通
配符(WILDCARD)开始, 索引将不被采用.
SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE USER_NO LIKE '%109204421'; 在这种情况下，ORACLE将使用全表扫描. WHERE USER_NO LIKE '109204421%'; 这钟情况ORACLE则不会进行全表扫描
6.用EXISTS替换DISTINCT
低效:
SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO
高效:
SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
7.减少对表的查询，特别在⼦查询中，能尽量少重复访问表，就减少。
8.如果分组当中含有查询条件，要改写为where条件进⾏过滤后，再进⾏分组，⽽不是直接进⾏ having 条件。
9.组合索引必须使用先导列,如果索引是建立在多个列上, 只有在它的第一个列(先导列) 被where子句引用时,优化器才会选择使用该索引.
create table multiindexusage ( inda number , indb number , descr varchar2(10)); create index multindex on multiindexusage(inda,indb); select * from multiindexusage where inda = 1; --走索引 select * from multiindexusage where indb = 1; --不走索引
10.sql语句中，where条件中要正确使用函数或者表达式，不正确使用，会进行字段类型转换，字段上索引失效
下面语句会使索引失效，并且把数据库库中所有记录的log_time时间值转换
为字符后，和条件中的字符串进行比较，走全表扫描，sql 执行效率大大降
低。
select log_time from account_login where to_char(log_time,'yyyy-mm
-dd hh24:mi:ss')='2009-04-01 00:00:00';
正确写法，使用to_date函数
select log_time from account_login where log_time=to_date('2009-0
4-01 00:00:00','YYYY-MM-DD HH24:MI:SS');当比较不同数据类型的数据时, ORACLE自动对列进行简单
的类型转换.
假设EMP_TYPE是一个字符类型的索引列. SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE USER_NO = 109204421 这个语句被ORACLE转换为: SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE TO_NUMBER(USER_NO) = 109204421 因为内部发生的类型转换, 这个索引将不会被用到!
11.避免在索引列上使用函数.WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．
低效： SELECT … FROM DEPT WHERE SAL * 12 > 25000; 高效: SELECT … FROM DEPT WHERE SAL > 25000/12;
12.避免在索引列上使用NOT
通常，我们要避免在索引列上使用NOT, NOT会产生在和在
索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就
会停止使用索引转而执行全表扫描.
低效: (这里,不使用索引) SELECT … FROM DEPT WHERE DEPT_CODE NOT = 0; 高效: (这里,使用了索引) SELECT … FROM DEPT WHERE DEPT_CODE > 0;
13.避免在索引列上使用 IS NULL和IS NOT NULL
避免在索引中使用任何可以为空的列，ORACLE将无法使用该
索引 ．对于单列索引，如果列包含空值，索引中将不存在此记
录. 对于复合索引，如果每个列都为空，索引中同样不存在此
记录. 如果至少有一个列不为空，则记录存在于索引中．
如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记
录的A,B值为(123,null) , ORACLE将不接受下一条具有相同
A,B值（123,null）的记录(插入). 然而如果所有的索引列都为
空，ORACLE将认为整个键值为空而空不等于空. 因此你可以
插入1000条具有相同键值的记录,当然它们都是空!
因为空值不存在于索引列中,所以WHERE子句中对索引列进行
空值比较将使ORACLE停用该索引.
任何在where子句中使用is null或is not null的语句优化器是
不允许使用索引的。
14.在含有子查询的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 TABLE WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604)
15.where 条件后⾯的的条件解析是从下向上，从后到前解析执⾏的，所以可以把过滤数据量较多的条件放在最后⾯。
（在mysql中正好相反，mysql中是从左到右）
16.关联查询时，选择好主表。oracle解析器对from 后⾯的表的解析是从右到左的，所以把数据量较⼩的表作为主表，然后和其他表进⾏关联，假如存在三个以下表，把同时交叉关联的表作为主表，提⾼查询效率。即大表写前面，小表写后面，过滤多的在后，同时交叉的在后。（mysql刚好相反，mysql尽量把小表写在前面，或者说写在左边）
如果从emp表查到的数据比较少或该表的过滤条件比较确定，能大大缩小查询范围，则将最具有选择性部分放在WHERE子句中的最后： select * from emp e,dept d where d.deptno >10 and e.deptno =30 ; 如果dept表返回的记录数较多的话，上面的查询语句会比下面的查询语句响应快得多。 select * from emp e,dept d where e.deptno =30 and d.deptno >10 ;
核心思想.
合理使用索引，避免索引失效和全面扫描的情况
优化建议
1.可以使用执行计划，执行计划可以查看相关查询条件是否进⼊索引，方便定位问题
2.避免会导致全表扫描的操作，如distinct、Union、在索引列使用函数、计算、转换、NOT、IN、NOT IN等等
13.1 数据库的设计优化
1、合理设计数据表结构。
合理选择建模方法（范式建模、维度建模）、字段、数据类型、约束条件、表空间大小。
（1）索引优化。
创建合适的索引，避免全表扫描，加快查询速度。要注意索引数量不能过多。
在使用索引的时候，也要尽量避免产生回表，尽管这很多时候很难避免。回表：使用普通索引的时候，如果索引里不包含全部要查找的字段，则需要回到表中查找需要的字段，这个过程也叫做回表。
（2）分区表优化。
通过合理分区减少查询的数据量，加快查询速度。
（3）计算方式优化。
通过分布式、并行计算，加快查询速度。
13.2 SQL调优
1、尽量少用子查询。子查询的结果集会占用内存，影响查询效率。
2、使用表别名。简化SQL语句，就减少了SQL解析，加快了查询速度。
3、减少以下效率低关键字的使用。
（1）union并集去重、minus差集、intersect交集的集合运算，这些关键字效率很差；
（2）用exists代替in、distinct关键字；
（3）or的效率比较低，可以使用union all去进行替换；
（4）模糊查询like；
4、使用临时表。简化SQL语句，就减少了SQL解析，加快了查询速度。
5、尽量不用排序order by。排序是相当耗费资源的。
6、避免使用select *，会导致全表扫描，造成不必要的资源开销且增加了查询时间。所以尽量只查需要的字段。
7、有大量DML语句时多使用commit，可以释放缓存，同时释放锁资源。
8、表连接的调优。
表的内部逻辑有三种不同的算法连接方式：
（1）hash join(哈希连接)
定义：等值关联，性能好，但占用内存较大。它是将被驱动表和其计算的hash值表一起放入缓存，然后在驱动表中逐条取数据匹配相应hash值，再精确匹配数据。所以，适用于大型数据集或大规模连接。
（2）nested loops(嵌套循环连接)
定义：内存占用小，但性能好。它是依次从驱动表中取一条数据，遍历被驱动表，将匹配的数据放入缓存中。所以适用于两表的数据差异大得情况。
（3）merge join(排序归并)
定义：不等关联，将关联的a表跟b表分别进行排序，生成临时的两张表后，随机取一张表逐条抽取记录与另一张表匹配。
以上的3中连接方式Oracle的优化器会评估查询的条件、表大小、索引使用等因素，并选择最适合的表连接算法来执行查询。当然也可以采用hints优化器强制改变连接方式：
改变表连接的逻辑：
/*+use_hash(a b) */ 强制使用哈希连接
/*+use_merge(a b)*/ 强制使用排序归并
/*+use_nl(a b) */ 强制使用嵌套连接
/*+leading(a b) */ a一定要是小表(驱动表)，b一定是大表(匹配表)
/*+ full(a) */ 强制全表扫描
/*+ index(表名 索引名) */ 强制使用索引
/*+ parallel(8) */ 强制使用并行的资源，来执行这个sql语句
13.3 参数配置优化
合理调整数据库的参数配置，如SGA和PGA的大小调整、日志和缓存的配置、并发连接数的设置等。主要参数有：SGA_TARGET（SGA）、DB_BLOCK_SIZE（块）、UNDO_RETENTION、SORT_AREA_SIZE（排序时内存）、LOG_BUFFER（日志缓存）。
13.4 硬件优化
一般是增加内存、磁盘。还可以采用集群
13.5 优化常用工具
SQL调优时常用的几个监控和诊断工具：
1、执行计划。
可以通过PLSQL或终端查看执行计划SQL语句执行顺序、CPU耗费、IO耗费等。（PLSQL中在编译计划窗口打开SQL即可查看）
2、 SQL Trace。
可以跟踪SQL语句的执行情况，包括执行时间、IO操作、锁等信息。
打开方法：
用管理员用户打开session的trace：
ALTER SESSION SET SQL_TRACE = TRUE;
查看trace文件的存储位置：
SELECT value FROM V$DIAG_INFO WHERE name= 'Diag Trace';
在该目录下打开文件即可。
3、AWR报告。
通过AWR报告查看数据库的性能指标资源使用情况，比如CPU使用率、内存使用率、IO等待时间等；以及top10的SQL分析。
打开方法：
1.终端下登陆dba用户：（验证是否连接）
sqlplus / as sysdba
2.然后输入下面这句：
@?/rdbms/admin/awrrpt.sql
3.输入 report_type 的值:
html或text
4.输入 num_days 的值:（1就是今天，2就是昨天和今天，依次排列）
1
5.输入 begin_snap 的值:（开始的snap id）
按照实际输入
6.输入 end_snap 的值:
按照实际输入
7.输入 report_name 的值:
文件名.html
8.然后去相应的目录找到该文件用网页打开即可
具体更多的指标解读分析请查询相关的知识。
13.6 优化思路
对于Oracle已存在的SQL语句运行很慢的问题的处理：
先通过执行计划或AWR报告获取该语句影响运行存在的问题，制定合适的方案。
数据非常大，那么建分区，只查询数据所在的分区表，避免整张大表的查询。
查看有没有导致索引失效的语句。
给需查询的字段创建索引。
查找SQL语句中有无低效率的关键字。
SQL语句频繁的对硬盘进行了读写，可以把语句写成代码块。
使用并行或分布式计算。
有内存的原因则增加内存。
12.分区表
当表的数据量不断增大，查询数据的速度就会变慢，性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。
分区表的划分方式有4种：范围分区、hash分区、列表分区、组合分区。
12.1 范围分区
关键字：range
最常用的一种分区，范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。
常用于范围分区的字段：数值范围类（比如工资区间）、时间范围类（比如一月一月的来）。
--范围分区（用sal列进行范围划分） create table amp( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) )partition by range(sal) ( partition sal_0_1000 values less than (1001), partition sal_1001_2000 values less than (2001), partition sal_2001_3000 values less than (3001), partition sal_3001_maxv values less than (maxvalue) ); --录入数据检验 insert into amp select * from emp; select * from amp partition(sal_0_1000);
12.2 hash分区
关键字：hash
Hash分区通过数据库的内部的哈希算法，将所有的行，根据数据，放到不同的分区中进行保存。
Hash分区常用于划分没有啥规律的字段。
--hash分区(划分没有规律的字段),本例子划分4个区 create table bmp( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) )partition by hash(ename) ( partition p1, partition p2, partition p3, partition p4 ); --录入数据检验，查看分区1的数据 insert into bmp select * from emp; select * from bmp partition(p1);
12.3 列表分区
关键字：list
该分区的特点是某列的值只有几个，并且存在一样的规律，基于这样的特点我们可以采用列表分区。
-比如说性别字段，就可以分为男女两个分区。 create table cmp( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) )partition by list(deptno) ( partition dept10 values(10), partition dept20 values(20), partition dept30 values(30), partition dept40 values(40) ); --录入数据检验 insert into cmp select * from emp; select * from cmp partition(dept10);
12.4 组合分区
组合分区就是将范围分区、hash分区、列表分区组合起来进行分区。
写法： create table 表名( 列名 数据类型 )partition by 父分区类型(列名) subpartition by 子分区类型(列名) ( partition 父分区名字 values 分区规则( subpartition 子分区名字 values 分区规则， subpartition 子分区名字 values 分区规则， ... ) );
--组合分区(以列表——范围为例子) create table dmp( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) )partition by list(deptno) subpartition by range(sal) ( partition d10 values(10) ( subpartition sal_10_1000 values less than (1001), subpartition sal_10_2000 values less than (2001), subpartition sal_10_maxv values less than (maxvalue) ), partition d20 values(20) ( subpartition sal_20_1000 values less than (1001), subpartition sal_20_2000 values less than (2001), subpartition sal_20_maxv values less than (maxvalue) ), partition d30 values(30) ( subpartition sal_30_1000 values less than (1001), subpartition sal_30_2000 values less than (2001), subpartition sal_30_maxv values less than (maxvalue) ), partition d40 values(40) ( subpartition sal_40_1000 values less than (1001), subpartition sal_40_2000 values less than (2001), subpartition sal_40_maxv values less than (maxvalue) ) ); --录入数据检验 insert into dmp select * from emp; --共4个主分区，12个子分区 select * from cmp partition(d10);
12.5 分区中分析常用语句
--增加分区subpartition alter table 表名 modify partition 父分区名 add subpartition 子分区名; --删除分区subpartition alter table 表名 drop subpartition 子分区名; select * from user_tables; --查看当前用户所有的表的信息 select * from user_tab_columns;--查看当前用户所有表的列信息 select * from user_tab_partitions; --查看用户所有的分区表信息 --主分区数据字典表 select * from user_tab_partitions; --主分区数据字典表 SELECT * FROM user_tab_partitions WHERE TABLE_NAME='EMP_RANGE'; --子分区数据字典表 select * from user_tab_subpartitions; --删除一个表的数据是 truncate table table_name; --删除分区表一个分区的数据是 alter table table_name truncate partition p2;
11.触发器
触发器是一种数据库对象。在事先为某张表绑定一段代码，当表中的数据发生增、删、改的时候，系统会自动触发代码并执行。
作用：检查输入的数据；实时备份表格的数据；记录表格操作的日志。
注：触发器发生错误时，前面用户已经执行成功的操作会被撤销，类似事务的回滚。
触发器在业务上主要应用于数据库的备份和审计。
触发器的类型分为：前置触发器、后置触发器。
区别：
触发时间：前置触发器是在执行DML之前被激活；而后置触发器是在执行DML之后被激活。所以前置触发器可以在DML之前验证或修改数据；而后置触发器在DML之后对数据进行处理，常用于日志记录和数据统计。
功能上：前置触发器可以做增、删、改的操作；而后置触发器不能，只能做select操作。
11.1 用法
创建触发器： create or replace trigger 触发器名字 before|after insert or update or delete on 表名 for each row begin 执行的sql语句; end; --前置触发器：before --后置触发器：after 删除触发器： drop trigger 触发器名称;
11.2 案例（前置触发器--验证数据）
需求：在emp表中如果要去更新用户的工资，新增的用户，工资不能超过2000元；老用户涨工资不能超过原来工资的10%；禁止删除岗位PRESIDENT。
create or replace trigger check_emp_sal
before insert or update or delete on emp
for each row
begin
if inserting then
if :new.sal>2000 then
raise_application_error('-20007','新员工工资不能超过2000');
end if;
elsif updating then
if :new.sal>:old.sal*1.1 then
raise_application_error('-20008','老员工涨工资不能超过原来的10%');
end if;
else
if :old.job='PRESIDENT' then
raise_application_error('-20006','不能删除岗位是PRESIDENT的员工信息');
end if;
end if;
end;
--这里报错：无法对sys拥有的对象创建触发器，换个普通用户，把emp表备份出来，再创建触发器
create table emp as select * from scott.emp;
--测试触发器
insert into emp values(6666,'ADDFD66','CLERK',null,date'2021-09-20',2001,null,40);
insert into emp values(6667,'ADDFD67','CLERK',null,date'2021-09-20',1500,null,40);
update emp set sal=4000 where empno=7369;
update emp set sal=850 where empno=7369;
delete from emp where job='CLERK' and sal<2000;
delete from emp where job='PRESIDENT';
经测试，结果符合预期，满足需求。
注：
1.sys的对象不能用来创建触发器，会报错。
2. "："有2中意思；
一：给变量赋值， 如， names varchar2(10) :='aa'; 这是把"aa"赋值给变量names。
二：表示引用，即引用表中字段所对应的值，如：emp表中有个name为“Bob”，那么 : old.name 的值就是"Bob"。只是表示引用的时候，只能出现在触发器里面。
3:NEW 和:OLD使用方法和意义，
new 只出现在insert和update时，old只出现在update和delete时。
在insert时new表示新插入的行数据，update时new表示要替换的新数据、old表示要被更改的原来的数据行，delete时old表示要被删除的数据。11.3 案例（后置触发器--审计）
触发器的另一个常见用途是为了之后审计的目的而记录的对数据库的修改。
比如：当一个人增加或删除了某条记录的时候，我们可以把这个操作记录下来。这样就知道了谁进行了什么样的操作。 需求：对dept表进行增删改，建立记录其改动的操作的审计表。 --1.先创建审计表。 create table dept_audit( user_name varchar(64), action_type varchar(64), action_date date, new_deptno number(2), old_deptno number(2), new_dname varchar(16), old_dname varchar(16), new_loc varchar(16), old_loc varchar(16) ); select *from dept_audit; --2.创建触发器： create or replace trigger after_dept after insert or update or delete on dept for each row begin if inserting then --dept表插入数据时 insert into dept_audit values(user,'insert',sysdate,:new.deptno,'',:new.dname,'',:new.loc,''); elsif updating then --dept表更新数据时 insert into dept_audit values(user,'update',sysdate,:new.deptno,:old.deptno,:new.dname,:old.dname,:new.loc,:old.loc); else --dept表删除数据时 insert into dept_audit values(user,'delete',sysdate,'',:old.deptno,'',:old.dname,'',:old.loc); end if; end; --3.验证触发器的结果 insert into dept values(60,'java','chongqing'); update dept set dname='python' where deptno=32; delete from dept where deptno=60; select * from dept_audit; 结果显示： 这里如果换个用户A登陆来对YANGFENG用户下的dept表的数据进行修改，那么user_name的数据也会变成用户A。