Oracle SQL语句之常见优化方法总结

1、用EXISTS替换DISTINCT

当SQL包含一对多表查询时,避免在SELECT子句中使用DISTINCT,一般用EXIST替换,EXISTS
(低效):

SELECT DISTINCT USER_ID,BILL_ID FROM USER_TAB1 D,USER_TAB2 E  W WHERE D.USER_ID= E.USER_ID;

(高效):

SELECT USER_ID,BILL_ID FROM USER_TAB1 D WHERE EXISTS(SELECT 1  FROM USER_TAB2 E WHERE E.USER_ID= D.USER_ID);

2、尽量多使用COMMIT： 

COMMIT所释放的资源: 

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

 b. 被程序语句获得的锁。

 c. redo log buffer 中的空间。 

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

3、用EXISTS替代IN、用NOT EXISTS替代 NOT IN：

（高效）

SELECT A.* FROM TEMP(基础表) A WHERE AGE > 0 

A AND EXISTS(SELECT 1 FROM TEMP1 WHERE A.ID= ID AND NAME='TOM');
 ( 低效 )

SELECT A.* FROM TEMP(基础表) A WHERE AGE > 0

A AND A.ID IN(SELECT ID FROM TEMP1 WHERE NAME ='TOM');

4、用>=替代>： 

高效:  SELECT * FROM TEMP WHERE ID >=4;
低效:  SELECT * FROM TEMP WHERE ID >3;
区别:前者DBMS将直接跳到第一个ID等于4的记录而后者将首先定位到ID=3的记录并且向前扫描到第一个ID大于3的记录。

5、避免在索引列上使用NOT： 

 NOT会产生在和在索引列上使用函数相同的影响。当ORACLE遇到NOT,就会停止使用索引转而执行全表扫描。

6、避免在索引列上使用计算： 

 WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。 

低效 ： SELECT … FROM TEMP WHERE SAL * 12 > 25000;

高效: :  SELECT … FROM TEMP WHERE SAL > 25000/12;

7、用UNION替换OR(适用于索引列): 

用UNION替换WHERE子句中的OR将会起到较好的效果。对索引列使用OR将造成全表扫描。注意,以上规则只针对多个索引列有效。如果有column没有被索引, 查询效率可能

会因为你没有选择OR而降低。

高效:

SELECT * FROM USER_TAB1 WHERE USER_ID = 10 

UNION 

SELECT * FROM USER_TAB1 WHERE AGE = 20;

低效: 

SELECT * FROM USER_TAB1 WHERE USER_ID = 10 OR AGE = 20;  

8、总是使用索引的第一个列： 

如果索引是建立在多个列上,只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引。 当引用索引的第二个列时,优化器使用了全表扫描而忽略了索引。

9、更新数据

UPDATE  A  set sex= (select sex from B where A.name = b.name)

10、分页查询

1、select * from (select a.*,rownum rn from ( select* from table_name ) a where rownum<=number_hi)where rn >=number_lo
取number_lo到number_hi的数；
2、select * from (select a.*,rownum rn from ( select* from table_name ) a where rownum<=number_hi)where rn between number_lo and number_hi

11、根据时间参数获取次数

months_between(date '2019-12-1',expire_date)

12、字符串查询不出来：
可能是字符串长度导致的
select * from ams1.sc_remind_240@lnk_zwa where length(user_id)=14 ；
select * from ams1.sc_remind_240@lnk_zwa where length(user_id)<>14 ；

13、通过分组，去重重复数据

select A,B FROM DUAL GROUP BY A,B

14、按日期分组

select to_char(create_date, 'yyyymmddhh24'),
count(1),
max((tf_date-create_date)*86400000),
min((tf_date-create_date)*86400000), ---日期做减法，求取时长
sum((tf_date-create_date)*86400000) / count(1) from dual group by to_char(create_date, 'yyyymmddhh24')

15、选择最有效率的表名顺序(只在基于规则的优化器中有效)

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.
例如: 表 TAB1 16,384 条记录
表 TAB2 1 条记录
选择TAB2作为基础表 (最好的方法)
select count(*) from tab1,tab2 执行时间0.96秒
选择TAB2作为基础表 (不佳的方法)
select count(*) from tab2,tab1 执行时间26.09秒
如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.
例如: EMP表描述了LOCATION表和CATEGORY表的交集.
SELECT *
FROM LOCATION L ,
CATEGORY C,
EMP E
WHERE E.EMP_NO BETWEEN 1000 AND 2000
AND E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
将比下列SQL更有效率
SELECT *
FROM EMP E ,
LOCATION L ,
CATEGORY C
WHERE E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
AND E.EMP_NO BETWEEN 1000 AND 2000

16、 WHERE子句中的连接顺序．
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
例如:
(低效,执行时间156.3秒)
SELECT …
FROM EMP E
WHERE SAL > 50000
AND JOB = ‘MANAGER’
AND 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO);
(高效,执行时间10.6秒)
SELECT …
FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO)
AND SAL > 50000
AND JOB = ‘MANAGER’; 

17、SELECT子句中避免使用 ‘ * ‘
当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*’ 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间. 

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

参考链接

https://blog.csdn.net/dtjiawenwang88/article/details/74892245