续（上期）
21、查'低效执行'的SQL语句的语句：
SELECT executions,
disk_reads,
buffer_gets,
round((buffer_gets - disk_reads) buffer_gets,
2) hit_radio,
round(disk_reads executions,
2) reads_per_run,
sql_text
FROM v$sqlarea
WHERE executions > 0
AND buffer_gets > 0
AND (buffer_gets - disk_reads) buffer_gets < 0.8
ORDER BY 4 DESC;
22、删除重复记录：
最高效的删除重复记录方法：
DELETE FROM temp e
WHERE 1 = 1
AND e.rowid > (SELECT MIN(x.rowid)
FROM temp x
WHERE 2 = 2
AND x.temp_no = e.temp_no);
23、减少对表的查询：
在含有子查询的SQL语句中,要特别注意减少对表的查询。
SELECT tab_name
FROM tables
WHERE 1 = 1
AND (tab_name, db_ver) = (SELECT tab_name,
db_ver
FROM tab_columns
WHERE version = 604);
24、避免使用耗费资源的操作:
带有DISTINCT、UNION、MINUS、INTERSECT、ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能。DISTINCT需要一次排序操作,而其他的至少需要执行两次排序。
通常,带有UNION、MINUS、INTERSECT的SQL语句都可以用其他方式重写。
如果数据库的SORT_AREA_SIZE调配得好,使用UNION,MINUS,INTERSECT也是可以考虑。
25、优化GROUP BY:
提高GROUP BY 语句的效率,可以通过将不需要的记录在GROUP BY之前过滤掉。
下面两个查询返回相同结果但明显第二个效率更高。
低效:
SELECT job,
AVG(age)
FROM temp
GROUP BY job
HAVING job = 'STUDENT' OR job = 'MANAGER';
高效:
SELECT job,
AVG(age)
FROM emp
WHERE job = 'STUDENT'
OR job = 'MANAGER'
GROUP BY job;
26、根据需要用UNION ALL替换UNION：
当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并,然后在输出最终结果前进行排序。如果用UNION ALL替代UNION, 这样排序就不是必要了。效率就会因此得到提高。需要注意UNION ALL将重复输出两个结果集合中相同记录。因此要从业务需求使用UNION ALL的可行性。UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存。对于这块内存的优化也很重要。
低效：
SELECT user_id,
bill_id
FROM user_tab1
WHERE age = '20'
UNION
SELECT user_id,
bill_id
FROM user_tab2
WHERE age = '20';
高效:
SELECT user_id,
bill_id
FROM user_tab1
WHERE age ='20'
UNIONALL
SELECT user_id,
bill_id
FROM user_tab2
WHERE age ='20';
27、用EXISTS替换DISTINCT：
当SQL包含一对多表查询时,避免在SELECT子句中使用DISTINCT,一般用EXIST替换,EXISTS查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果。
低效：
SELECTDISTINCT user_id,
bill_id
FROM user_tab1 d,
user_tab2 e
WHERE d.user_id = e.user_id;
高效:
SELECT user_id,
bill_id
FROM user_tab1 d
WHERE EXISTS(SELECT1
FROM user_tab2 e
WHERE e.user_id = d.user_id);
28、用EXISTS替代IN、用NOTEXISTS替代 NOT IN：
在基于基础表的查询中经常需要对另一个表进行联接。在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率。在子查询中,NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下,NOT IN都是最低效的(要对子查询中的表执行了一个全表遍历)。所以尽量将NOT IN改写成外连接(OuterJoins)或NOT EXISTS。
低效：
SELECT a.*
FROM temp(基础表) a
WHERE age >0
AND a.id IN(SELECT id
FROM temp1
WHERE NAME='TOM');
高效：
SELECT a.*
FROM temp(基础表) a
WHERE age >0
AND EXISTS(SELECT1
FROM temp1
WHERE a.id =id
AND NAME='TOM');
29、用索引提高效率：
索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了复杂的自平衡B-tree结构。通常通过索引查询数据比全表扫描要快。当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引。同样在联结多个表时使用索引也可以提高效率。
另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证。那些LONG或LONG RAW 数据类型, 你可以索引几乎所有的列。通常在大型表中使用索引特别有效。在扫描小表时,使用索引同样能提高效率。
使用索引虽能得到查询效率的提高,但也必须注意到它的代价。索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改。这意味着每条记录的INSERT、DELETE、UPDATE将为此多付出4、5 次的磁盘I/O。因为索引需要额外的存储空间和处理,一些不必要的索引反而会使查询反应时间变慢。
定期的重构索引:
ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>
30、避免在索引列上使用计算：
WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。
低效：
SELECT …
FROM temp
WHERE sal * 12 > 25000;
高效：
SELECT …
FROM temp
WHERE sal > 25000 / 12;
下期更精彩；
本文分享自微信公众号 - Oracle优化大师，如有侵权，请联系 service001@enmotech.com 删除。
