- 1. Oracle 高性能SQL优化航天信息股份有限公司
- 2. 什么是Oracle性能管理 Oracle性能管理是一种利用已证实过的方法，反复试验、判断和实现优化方案的循序渐进的过程。
SQL语句优化是性能管理的重要组成部分。 概述
- 3. Oracle性能管理的类型（1） 主动的性能管理？
以一种高性能的体系结构思想指导设计和开发完善的系统。
它也指定期监控系统的性能，注意各种苗头，在它们成为实际问题前加以解决。 概述
- 4. Oracle性能管理的类型（2） 被动的性能管理？
涉及性能评估、故障排除、优化以及在现有硬件和软件体系结构内对环境进行的优化。它在出现问题时进行处理。
相对于所获得的性能收益，其代价一般较高。 概述
- 5. 什么是优化 优化是有目的地更改系统的一个或多个组件，使其满足一个或多个目标的过程。
对Oracle来说，优化是进行有目的的调整组件以改善性能，即增加吞吐量，减少响应时间。 概述
- 6. SQL优化的目标去掉不必要的大型表的全表扫描
缓存小型表的全表扫描
检验优化索引的使用
检验优化的连接技术
检查子查询概述
- 7. 不同优化阶段性能收益设计阶段开发阶段测试阶段运行维护阶段生命周期效果概述
- 8. SQL调整的障碍 对特定的SQL生成器的调整
调整不可再用的SQL语句
来自管理方面的抵制
来自SQL程序员的抵制概述
- 9. SQL语句处理过程SQL执行
- 10. SQL语句的解析过程调入库缓存 (内存)
检验安全性
检验语法
重新格式化查询 (查询重写)SQL执行
- 11. 减少SQL解析的技术
将所有的SQL语句放置在存储过程中
在SQL语句中避免使用直接量SQL执行
- 12. 生成执行计划 对Oracle来说查询速度和查询效率是两个完全不同的概念。Oracle有两个优化目标，这两个目标分别反映了为了满足SQL的要求而使用的各自不同的通用手段。
最大速度 以最短的时间返回结果集
(Oracle的first_rows优化器模式)
最小资源占用 使用少量的机器和磁盘资源
(all_rows优化器模式)SQL执行
- 13. SQL语句性能评估的方法
SQL语句的执行计划
执行SQL语句所需要的时间SQL执行
- 14. 执行SQL语句所需要的时间SQL执行
- 15. 查看SQL语句的执行计划SQL执行
- 16. SQL语句的执行计划 表访问方式
全表扫描
散列获取
ROWID访问 索引访问方式
索引范围扫描
单个索引扫描
降序索引范围扫描
And-equal过滤器 连接操作方式
嵌套循环连接
散列连接
排序合并连接
Star连接
Connect by连接SQL执行
- 17. SQL语句的优化器优化器种类:
基于规则的优化器-RBO
基于成本的优化器-CBO
优化器模式:
Rule
Choose
First_rows
All_rowsSQL优化器
- 18. 基于规则的优化器-RBO RBO不使用任何表或索引的统计数据,而是用试探法来确定到达数据的最佳访问路径。RBO根据迭代过程来生成执行计划，并且检查from子句中的每个表，以及在该查询中每个表与其他表的所有连接方式。每一个可行的路径根据它们所要消耗的成本进行排序，并选择成本最低的路径。 SQL优化器
- 19. RBO选择低成本的依据 01）ROWID读取
02）使用簇连接的单独记录
03）使用散列簇主键的单独记录
04）使用主键的单独记录
05）簇连接
06）散列簇主键
07）索引簇主键
08）复合主键
09）单字段索引
10）索引字段的结合范围查找
11）排序合并连接
12）索引字段上的MAX或MIN
13）索引字段上的ORDER BY
14）全表扫描 SQL优化器
- 20. 基于规则优化器的特性总是使用索引。
总是从驱动表开始。
只有在不可避免的情况下，才使用全表扫描。
任何索引都可以 有时会选择一个并非最理想的索引来对查询服务。SQL优化器
- 21. 基于规则的SQL语句优化更改基于规则的驱动表。
如果基于规则的优化器没有使用正确的索引，可通过索引提示强制使用我们想要使用的索引，或者在索引中混入数据类型的方式禁用那些不想的查询中使用的索引。
SQL优化器
- 22. 基于成本的优化器-CBO Oracle基于成本的优化器的创建目的是为基于规则的优化提供更加复杂的替代方式。
CBO 优化器需要了解表和索引统计数据的细节, SQL优化根据成本进行排序,并选择成本最低的路径。 SQL优化器
- 23. 表和索引的统计数据表数据
记录的数目
物理数据块的数目
索引数据
索引中惟一值的数目
索引中值的分布
索引的可选择性
索引簇因素 SQL优化器
- 24. 全表扫描
索引访问
快速完全索引扫描
SQL访问数据块的方法优化SQL表访问
- 25. 基于规则的优化器如果探测到 可用的索引，总是选择使用索引，否则使用全表扫描。
基于成本的优化器拥有有关表中数据的信息，当估计到全表扫描的成本低于索引访问的成本时，即使存在索引，它也选择全表扫描。
ALL_ROWS 优化器模式更倾向于全表扫描，更适用于批量处理的查询。
FIRST_ROWS 优化器模式确保以最快的速度返回记录。如果目标表中不存在可用索引，那么它将执行全表扫描。优化器如何选择全表扫描优化SQL表访问
- 26. 使用NULL条件的查询
对没有索引字段的查询
带有like条件且掩码开始端使用%字符
带有Not Equals条件的查询
内置函数使索引无效
使用ALL_ROWS提示
使用并行提示可能导致全表扫描的语法优化SQL表访问
- 27. 更改表访问方式将子查询替换为标准的连接
重新书写SQL语句以更改表访问方法
添加或删除索引
使用提示强制改变表访问方法
在话会级更改optimizer_goal参数
在实例级optimizer_mode
优化SQL表访问
- 28. SQL优化过程概述定位 ：定位不友好的、影响大的SQL语句
解释：用 PL/SQL或Explain Plan获得执行计划
调整：使用索引、提示和查询重写等手段对SQL语句进行优化。关注点是：执行计划和运行时间。过程概述
- 29. 调整SQL语句提示: 添加SQL提示会修改执行计划
索引: 添加B树索引去掉全表扫描
重新书写: 改变执行计划，尤其是在使用RBO时更改FROM子句中表的顺序时更明显。
位图索引: 查询中where子句中提及的所有低基数字段添加位图索引。
PL/SQL： SQL被替换为对PL/SQL软件包的调用，软件包中含执行查询所需的存储过程。过程概述
- 30. 评估全表扫描的合法性 对于原始排序的表 读取少于记录数40%的查询应使用索引范围扫描。反之，使用全表扫描。
对于未排序的表 读取少于记录数7%的查询应使用索引范围扫描。反之，使用全表扫描。全表扫描和并行查询
- 31. 对小表的全表扫描 select DISTINCT A.FUNC_DM, A.FUNC_MC, A.URL, A.FUNC_DM_F, A."DESC", A.YJCZBZ
from HTJS.HT_GNRKB A, HTJS.HT_ROLE_MENU B
where A.SWJG_JC >= 5 and B.ROLE_ID = '报税业务操作员' and not A.FUNC_DM = 'bs' and substr(A.FUNC_DM, 0, 2) = 'bs' and (B.YXBZ = 'Y' and substr(A.FUNC_DM, 0, length(B.FUNC_DM)) = B.FUNC_DM)
and A.FUNC_DM not in (select A.FUNC_DM from HTJS.HT_GNRKB A, HTJS.HT_ROLE_MENU B where
B.ROLE_ID = '报税业务操作员' and B.YXBZ = 'N‘
and substr(A.FUNC_DM, 0, length(B.FUNC_DM)) = B.FUNC_DM) ;全表扫描和并行查询
- 32. 该语句的执行计划全表扫描和并行查询
- 33. 避免全表扫描的方法添加B树索引
添加位图索引
添加基于函数的索引
强制CBO使用带有index提示的索引全表扫描和并行查询
- 34. Oracle并行查询 持久并行机制—不推荐
alter table table_name parallel degree N ;
单个查询的并行机制
select /*+ FULL(emp) PARALLEL(emp,5) */
ename
from emp; 全表扫描和并行查询
- 35. 设置最优的并行度服务器上CPU的数量
存储表所在磁盘数
如果根据分区实施并行化，则其中一个因素是基于分区划分的将被分区访问的分区的数目。
如果并行DML操作使用全局索引维护，所有全局索引中事务处理空闲表的最小值需要更新。
最优的并行度可以安全地设置为N-1，而N代表SMP或MPP簇中处理器的数量 全表扫描和并行查询
- 36. 并行查询和表连接
带有并行查询的嵌套循环连接
排序合并连接和并行查询
并行化散列连接 全表扫描和并行查询
- 37. 带有并行查询的嵌套循环连接 在嵌套循环连接中，通常使用索引对表进行连接。然而，可以创建一个执行计划，其中该执行计划调用嵌套循环连接，一般只对连接中的一个驱动表执行全表扫描。在大多数情况下，索引访问更快! 全表扫描和并行查询并行查询和表连接
- 38. 排序合并连接和并行查询 合并连接总是对表执行全表扫描，所以排序合并连接可以提供最理想的并行查询。排序合并连接最适合产生非常大结果集的查询，可能适合于没有where子句的大表连接，或没有可用索引对表进行连接的查询。 全表扫描和并行查询并行查询和表连接
- 39. 并行化散列连接 Oracle将驱动表读入hash_area_size中的一个RAM队列中，生成内存数组，并使用专用的散列方法将内存数组与更大的表连接起来。
对于等值连接操作，散列连接可能胜过嵌套循环连接，特别是驱动表小到可以完全装入hash_area_size的情况下。如果驱动表太大，散列连接会把临时段写入TEMP表空间中，导致查询速度降低。 全表扫描和并行查询并行查询和表连接
- 40. Oracle的排序 Oracle的排序通常发生在如下情况：
SQL语句中包含order by子句
SQL中包含group by子句
SQL中包含select distinct子句
创建索引时
SQL中包含union或minus
SQL优化器调用排序合并连接 优化SQL语句排序
- 41. 添加索引避免排序 避免全表扫描和不必要排序的最好方法是添加索引。使用first_rows模式时，Oracle通常使用索引代替排序操作，从而避免order by子句。优化SQL语句排序
- 42. 不必要的排序缺失索引
查询需要字段索引
排序合并连接
就会执行排序以连接关键字，通常嵌套循环连接好
使用distinct
索引中使用distinct子句通常会调用索引以去掉重复记录优化SQL语句排序
- 43. SQL查询中使用提示的重要原则(1) 仔细检查提示语法
最好使用完整注释语法，如使用/*+ hint*/
如果在查询中为表指定了别名，那么不能使用表名称
select /*+ index(e,dept_idx)*/
ename,deptno
from emp e;
不要在提示中使用模式名称，否则提示被忽略。
select /*+ index(scott.emp,dept_idx) */
ename
from emp; 使用提示进行优化
- 44. SQL查询中使用提示的重要原则(2)检验提示
如果提示指定不可用的访问路径，提示将被忽略。如索引范围扫描上指定一个parallel提示，则提示被忽略。提示和查询之间的矛盾并不是显而易见的，下面的emp表的ename不存在索引时，则
Select /*+ first_rows */
ename,deptno
from emp
order by ename;使用提示进行优化
- 45. 使提示无效条件Cluster与非簇表一同使用
Hash与非簇表一同使用
Hash_aj不存在子查询
Index指定索引不存在
Index_combine不存在位图索引
Merge_aj不存在子查询
Parallel调用的不是TABLE ACCESS FULL计划
Push_subq不存在子查询
Star事实表中存在不恰当索引
Use_concat在where子句中不存在多个or条件
Use_nl表中不存在索引 使用提示进行优化
- 46. 优化器提示(1)first_rows提示
是一种基于成本的优化方法，目的是提供最快的反应时间，要保证查询中所涉及的表及索引拥有统计数据。
一般来说，optimizer_mode=choose模式的SQL都应该使用first_rows和rule提示进行测试，观察运行性能是否有所提高。使用提示进行优化
- 47. 优化器提示(2) all_rows提示
是一种基于成本的优化方法，目的是提供最佳吞量和最小的资源消耗。倾向于使用全表扫描，而且不适用于OLTP数据库。在基于规则的数据库中添加提示时，要保证查询中所涉及的表及索引拥有统计资料。 使用提示进行优化
- 48. 优化器提示(3)rule提示
在怀凝CBO生成了非优化的执行计划时，通常在调整SQL语句时先尝试使用rule提示。
使用提示进行优化
- 49. 表连接提示(1)use_nl提示
强制对目标表执行嵌套循环连接。与其他连接不同，只需要驱动表的名称。嵌套循环连接是最古老的连接方式，它几乎总是与基于规则的优化方式共同使用。
select /*+ use_nl(e) */
e.ename,
hiredate,
b.comm
from emp e,bonus b
where e.ename=b.ename; 使用提示进行优化
- 50. 表连接提示(2)use_hash提示
散列连接是Oracle用以从驱动表向RAM区中装载记录的方法。当这两个表都非常大的情况下，散列连接通常与并行查询结合使用！
select /*+ use_hash(e,b) parallel(e,4) */
e.ename,hiredate,b.comm.
from emp e,bonus b
where e.name=b.ename ;
散列连接通常快于嵌套循环连接，特别是在驱动表已经在查询的where子句中过滤，只乘下少量记录的情况。Use_hash提示非常挑剔，必须满足很多条件，如统计数据。使用提示进行优化
- 51. 表连接提示(3)use_merge提示
强制执行一个排序合并操作，排序合并操作通常与并行查询结合使用，因为排序合并连接对表执行全表扫描。
排序合并连接最适用于生成大型结果集的查询，如日常报表和表细节总结查询，或用来处理不使用连接主键索引的表。
select /*+ use_merge(a,b) parallel(e,4) */
e.ename,
hiredate,
b.comm
from emp e,bonus b
where e.ename=b.ename ;使用提示进行优化
- 52. 表连接提示(4)star提示
star提示强制使用星型查询计划。前提是查询中存在至少三个表，而且在事实表中存在恰当的索引。比传统的连接方式快得多，传统的连接方式先将最小的引用表与事实表连接，再将其化的引用表与中间生成表连接。 使用提示进行优化
- 53. 表连接提示(4)-续星型连接工作过程：
Oracle先服务于小维表查询，并将结果连接到笛卡尔生产表中，其中这个表存储Oracle内存中。这个虚拟表包含来自所有相关维表的所有记录。这个虚拟表的主键是来自维表所有主键的组合。如果这个主键与事实表中组合索引相匹配，这个查询将很快处理。一旦确定了引用表的数目，则Oracle将基于事实表执行中间表的嵌套循环连接。 使用提示进行优化
- 54. 表连接提示(4)-续 使用star提示必须满足下列条件:
必须存在连接在一起的至少三个表，其中有一个大的事实表和几个小维表。
在事实表的字段上必须存在一个索引，这些字段将是每个表的连接主键。从Oracle8i开始，需要使用位图索引，而不是连接在一起的索引。
必须检验执行计划，以确保在执行连接时使用嵌套循环连接操作。 使用提示进行优化
- 55. 表反连接提示（1）merge_aj提示
在使用全表访问比索引访问更好的情况下，可以在NOT IN子查询中使用merge_aj提示以便执行反连接。
Select DNAME
From DEPT
Where
DEPTNO not in
(select /*+ merge_aj */ DEPTNO
from EMP
where JOB=’SALESMAN’)； 使用提示进行优化
- 56. 表反连接提示（1）-续 使用NOT IN的条件:
如果在子查询的字段中确实不存在空值，而又在子查询中使用了提示，外层查询块生成大量记录的情况下，才考虑使用NOT IN子查询。
根据连接的类型，可以考虑使用hash_aj或merge_aj提示执行反连接。而只有在NOT IN子句中要求的字段有非空限制的时候，反连接hash_aj或merge_aj才起作用。 使用提示进行优化
- 57. 表反连接提示（2）hash_aj提示
hash_aj提示放置于NOT IN子查询中用来在希望执行散列连接时，执行散列反连接。使用提示进行优化
- 58. Index提示(1) 使用Index提示的原则
如果表名或索引名拼写错误，查询将不使用提示。
Select /*+ index(erp,dept_idx) */ from emp;
表名在提示中必须指定
Select /*+ index(dept_idx) */ from emp;
如果在查询中使用表别名，那么提示必须使用表别名。
Select /*+ index(emp,dept_idx) */ from emp e;
索引名称是可选的，如果没有指定，优化器将指定表中“最佳的”索引，但不推荐！
Select /*+ index(e) */ from emp e; 使用提示进行优化
- 59. 指定索引提高性能的例子 select
DISTINCT FP_DM from
htjs.FP_SFD where
FP_DM like ‘%3300%’
and
SWJG_DM = ‘133010300’;
--IDX_FP_SFD_FPDM_SFLB_FPQSHM;
--INDEX_FP_SFD(SWJG_DM,SFLB,SFRQ);使用提示进行优化
- 60. 指定索引前的执行计划使用提示进行优化
- 61. 指定索引提高性能的例子 select /*+ index(fp_sfd,index_fp_sfd) */
DISTINCT FP_DM
from htjs.FP_SFD
where
FP_DM like ‘%3300%’
and
SWJG_DM = ‘133010300’
;使用提示进行优化
- 62. 指定索引后该例的执行计划使用提示进行优化
- 63. Index提示(2) and_equal提示
如果表拥有非惟一字段索引，而且你期望使用多个索引服务于该查询，那么可以使用and_equal提示。and_equal提示将合并这些索引，并使这些单独的索引操作时就像单个连锁索引一样。
and_equal提示需要指定目标表名和至少两个索引名，但索引名不能超过五个。使用提示进行优化
- 64. Index提示(2)-续 Select /*+ and_equal(emp,job_idx,mgr_idx) */
ENAME,
JOB,
MGR
From
EMP
Where
Job=’SALESMAN’
And
Mgr=7628
;使用提示进行优化
- 65. Index提示(3) index_desc提示
index_desc提示要求在范围扫描中使用降序索引。在使用MAX内置函数计算字段的最大值时，index_desc提示将提供更好的性能。
Select /*+ index_desc(emp,sal_idx) */
ENAME,
Max(SALARY)
From
EMP
Group by ENAME
; 使用提示进行优化
- 66. Index提示(4) index_combine提示
index_combine提示强制使用位图索引作为表的访问路径。index_combine提示要求以优化器对两个位图索引都执行ROWID交集操作。实际操作中最好指定表名和两个索引名。
Select /*+ rule(emp) index_combine(emp,dept_bit,job_bit) */
ENAME,JOB,DEPTNO,
MGR
From
EMP
Where
JOB=’SALESMAN’
And
DEPTNO=30
; --这种位图合并方法将在很大程度上降低大表的执行时间 使用提示进行优化
- 67. Index提示(5) index_ffs提示
索引快速完全扫描可以在不访问任何记录的情况下完成查询。优化器使用多块读取扫描索引中的所有数据块。
可以同时使用index_ffs提示和parallel提示，使索引快速完全扫描的速度更快。使用提示进行优化
- 68. Index提示(5)－续 index_ffs提示
create index
Dept_job_idx
on
Emp(job);
select
ename,job,deptno,mgr
from emp
where job='SALESMAN';
select /*+ index_ffs(emp,dept_job_idx) */
ename,job,deptno,mgr
from emp
where job='SALESMAN';使用提示进行优化
- 69. Index提示(6) use_concat提示
use_concat提示要求为查询中所有OR条件使用union all执行计划，并将这个查询重新书写为多个查询。
当一个查询对位图索引字段使用多个OR条件时，Oracle将自动使用位图索引。
如果有SQL查询的where子句中存在大量的OR条件，那么通常需要使用use_concat提示。 使用提示进行优化
- 70. Index提示(6)-续 use_concat提示
select
ENAME
from
EMP
where
deptno=10
or
sal<5000
or
job=’CLERK’;
供选择的解决办法:
1)有的索引字段基数较低，可以在deptno、sal、job上创建三个位图索引，并生成一个位图合并执行计划。
2）调用use_concat提示，将查询分解为三个独立的B树索引扫描。使用提示进行优化
- 71. Parallel提示 对于全表扫描来说，parallel提示要求表查询以并行模式执行，同时，并行查询从动进程的数量由degree参数决定。
select /*+ FULL(emp) PARALLEL(emp,) */
ename,
sal
from emp ;
需要同时使用full提示和parallel提示以确保调用全表扫描。如果优化器没有使用全表扫描，则parallel提示被忽略。 使用提示进行优化
- 72. 表访问提示(1)full提示
使用full提示最常见的情况是，已经确定索引范围扫描要读取大量表中数据块，而并行全表扫描将提高查询的速度。 使用提示进行优化
- 73. 表访问提示(2)簇表提示
簇通常用于两个表之间存在着一对多的关系，而大多数查询都是从主表向成员表访问的情况。将主表和成员表的记录存储在相同的数据块，从而一个单独的数据块输入输出可以读取来自两个表的记录。使用提示进行优化
- 74. 表访问提示(2)-续hash提示
select /*+ hash */
e.ename,
d.deptno
from
emp e,
dept d
where
e.deptno=d.deptno
and
deptno=20
; cluster提示
select /*+ cluster */
e.ename,
d.deptno
from
emp e,
dept d
where
e.deptno=d.deptno
and
deptno=20
; 使用提示进行优化
- 75. ordered 提示 在基于成本的优化器中，ordered提示要求按from子句中指定的顺序进行连接，而指定from子句中的第一个表作为驱动表。通常与其他提示结合使用，以确保多个表按照正确的顺序连接。从而免避过多的评估所花费的时间。
例如：
select /* ordered use_hash(emp,dept) use_nl(sal,bonus */
emp.ename,
dept.deptno
from
emp,
dept,
salgrade,
bonus
where … 使用提示进行优化
- 76. 子查询中的提示 SQL语句中的每一个子查询都可以使用提示来改进执行计划。位于外部查询的指定表的提示不会干涉子查询，子查询指定表的提示也不会干涉外部查询。
select A.EMPNO
from
DEPT B,EMP A,SALGRADE C
where
exists (
select /*+ INDEX(A) */
A.EMPNO
from DEPT B,EMP A,SALGRADE C
where
A.DEPTNO=B.DEPTNO …… )
and
A.DEPTNO=B.DEPTNO And A.JOB<>’clerk’ ; 使用提示进行优化
- 77. 调用基于规则的优化器设置init.ora参数optimizer_mode=rule
会话层使用alter session set optimizer_mode=rule
为基于成本的SQL添加rule提示：/*+ rule*/
基于规则优化器调整
- 78. 使用choose作为默认优化模式时存在的问题 使用choose作为默认优化模式时,根据CBO统计资料的存在与否来评估是使用RBO还是CBO。 使用choose模式的真正问题是，只要任何表或索引包含了统计资料，choose模式就假定你期望使用CBO。基于规则优化器调整
- 79. 默认优化器模式 选择具有最优总体性的默认优化器模式的目标是：最小化人工SQL调整的数量。
决定是否使用基于规则的优化模式作为默认值的过程是：首先使用rule模式运行一天生产数据库，接着使用first_rows模式再运行一天，然后根据最终用户的性能反馈和STATSPACK的性能测量，最后决定基于规则的优化是否可以作为合适的默认优化器模式。基于规则优化器调整
- 80. 基于规则的优化器驱动表位置
在RBO中，驱动表是from子句中的最后一个表。
select /*+ rule */
Emp.ename,
Emp.deptno,
Bonus.comm
from
Emp,
Bonus
where
Emp.ename=bonus.ename；驱动表和表基数
驱动表应当是返回最小数目记录的表，而不一定是拥有最少记录数的表。
select
customer_name
from
customer,order
where
customer.cust_nbr=
order.cust_nbr
and
order_status=’backordered’; 基于规则优化器调整
- 81. 有关调整基于规则查询的提示在RBO中进行优化是通过重新书写查询更改执行计划来实现的。主要建议是：
1）重新排序表名
2）重新排序布尔谓词
更改where子句中语句的顺序。Oracle将从SQL语句的底层按照与由AND分开的布尔表达式相反的顺序对SQL进行解析。
3）添加基于成本的提示
4）仔细评估连接方法
如果使用RBO，必须处理嵌套循环连接。尽管在RBO中可以使用排序合并连接，但应当将所有执行排序合并连接的RBO查询，替换为使用Oracle并行查询的基于成本的等价访问。 基于规则优化器调整
- 82. 基于规则的优化器无效的情况在基于规则的优化模式中，所有的索引都有同等的级别！
在有些情况下,基于规则的优化器无法选择优化的执行计划。这通常是因为RBO无法使用高级的索引结构和并行查询。即使在新功能的帮助下，RBO有时因为优化器没有意识到表和索引中惟一值的数量，可能选择了非优化的索引为查询服务。
基于规则的优化器不识别位图索引和基于函数的索引，将会导致全表扫描。基于规则优化器调整
- 83. 统计数据和基于成本的优化 使用基于成本优化的一个前提条件是，存在表和索引的统计数据。
对于表来说，统计数据包括记录数和表中记录平均长度等信息。
索引的统计数据包括：有关索引中惟一值的数量、索引中元素的数量，以及在表空间中物理存储等方面的信息。基于成本优化器调整
- 84. 基于成本优化和SQL 优化 Oracle8i基于成本的优化器已经可以与基于规则优化相兼容，那么许多SQL调整专业人员就可以使用基于成本的默认优化器模式，再对个体查询进行调整，从而达到最快的执行计划。
大多数SQL调整专业人员在遇到可疑的SQL语句时，做的第一件事就是添加rule提示，并察看生成的执行计划是否更加有效。
在读取任何记录之前，基于成本的优化器必须创建一个执行计划，这个执行计划告诉Oracle访问目标表和索引的顺序，CBO首先对不同的数据访问路径的成本进行评估，然后选择成本最低的路径。基于成本优化器调整
- 85. 基于成本的表连接通用原则：
嵌套循环连接和散列连接应当始终使用所有表上的索引，除了使用排序合并连接以外，在服务于表连接时，一般不要调用全表扫描。优化步骤：
1）执行计划中查找全表扫描。如果发现了则尝试做的第一件事是，使用index提示或rule提示，从而强制对所有的表使用索引。
2）尝试计算使用不同连接技术的查询的执行时间。基于成本优化器调整
- 86. 基于成本的表连接与Oracle版本 在Oracle8i之前版本的基于成本的优化器中存在的一个缺陷是，如果在查询中使用了多于4个表的连接，那么基于成本的优化器几乎总是为其中的一个表调用全表扫描。
在Oracle8i及以后版本中，仍然可能有这样的非优化的查询计划。基于成本优化器调整
- 87. 带有子查询的SQL语句的优化（1） 首先应该检查where子句，并确定该子查询是非关联子查询还是关联子查询。
标准连接通常比子查询快，因此，要仔细评估所有个体SQL查询，以决定是否子查询可以更改为标准连接。基于成本优化器调整
- 88. 带有子查询的SQL语句的优化（2）Select
*
from
student
where
student_id in
(select
student_id
from registration
where grade=’A’
)
; select
student.*
from
student,
registration
where
student.student_id=
registration.student_id
and
registration.grade=’A’
; 基于成本优化器调整
- 89. 优化复杂的布尔查询（1）布尔谓词中的复合OR条件
1：OR中包含相同字段的数值
2：OR条件引用不同的字段基于成本优化器调整
- 90. 优化复杂的布尔查询（2）对于所有字段都相同的OR类型，基于成本的执行计划将取决于是否存在索引。
如果一个查询的位图索引字段存在多个OR条件，那么Oracle将自动使用位图索引。 Select /*+ first_rows */
ENAME
From
EMP
Where
Deptno in (10,20,30,40)
And
(
job=’CLEAK’
or
job=’SALESMAN’
or
job=’SECRETARY’
) ；基于成本优化器调整
- 91. 布尔谓词中的复合AND条件(1)Select /*+ first_rows */
ENAME，
JOB，
DEPTNO
From
EMP
Where
Deptno = 10
and
Sal < 5000
and
Job=’CLERK’; 三个字段都有B树索引，CBO选择具有选择最具有选择性的索引。
如果以上三个字段上建立三个位图索引，则调用了BITMAP CONVERSION执行计划的变更。 基于成本优化器调整
- 92. 布尔谓词中的复合AND条件(2)select /*+ index_combine(emp,emp_deptno_bit,emp_JOB_bit,emp_sal_bit) */
ENAME，
JOB，
DEPTNO
from
EMP
where
Deptno = 10
and
Sal < 5000
and
Job=’CLERK’; 有位图索引时使用index_combine 提示将对表强制使用位图访问路径。如果语句中没有这个提示，优化器将选择表访问成本最低的位图索引的布尔组合。使用这个提示指导优化器执行两个位图的ROWID 交集操作。在实际操作中，最好指定表名称和提示中的两个索引名称。 基于成本优化器调整
- 93. 基于规则的优化器和基于成本的优化器RBO非常稳定，而且结果可以预测；而CBO更加智能，但是通常不可预测。
随着CBO一个新版本的产生，使用RBO的数据库的数量就会降低。
如果使用基于成本的优化器作为默认值，可通过使用rule提示来覆盖所指定的查询。
如果使用基于规则的优化器作为默认值，可通过添加基于成本提示并分析所选定的表索引来调整SQL查询。
在RBO中，from子句中表的顺序决定表的连接顺序。From子句中最后一个表是驱动表
RBO中最常见的缺陷是，它无法了解表中每个索引的可选择性。
where子句中布尔谓词的顺序也会影响RBO的行为。 基于规则优化器调整
- 94. 什么是索引索引是建立在表的一列或多个列上的辅助对象，它有利于快速访问表的数据。
索引由于其内在的结构，它具有某些内在的开销，这些开销依赖于为了检索由索引中ROWID指定的行所访问的表中的块数，并且这个开销可能会超过进行全表扫描的成本。用索引优化SQL
- 95. 何时使用索引建立索引的目的之一是减少I/O操作，如果当一个查询使用索引时相对于全表扫描执行了更多的I/O操作，则使用索引的意义会明显降低。
主键：即主键约束，Oracle会自动建立惟一性索引，因此不要再为主键中的列建立索引了。用索引优化SQL
- 96. 具有吸引力的索引 非惟一索引
惟一索引
位图索引
基于函数的索引
反向键索引
降序索引
索引编排表局部有前缀分区索引
局部无前缀分区索引
全局有前缀分区索引
散列分区索引
组合分区索引用索引优化SQL
- 97. 怎样建立最佳索引建立索引不是一个很容易完成的任务，因为它完全依赖于应用程序的数据查询模式。
如果了解应用程序，则问题不复杂；如果了解数据访问的最常用的方法，则能够建立最佳索引是肯定的；总之，必须确定最常使用列的列表，并且作出关于要建立的索引数量、需要的列组合以及将建立的索引的类型的决定。 用索引优化SQL
- 98. 建立最佳索引需要回答的问题 特定表中的数据访问的最常用的列组合是什么？
打算在其上建立索引的一组给定列，其选择性是什么？
如果一些列始终有值并且相对惟一，则它们应该是索引的前导列，为建立索引，按照可能具有惟一值的概率，对这些列进行子程序排列。
在where子句中引用的所有列都需要进行索引吗？
如果列具有很低的数据基数，并且或者可具有空值的话，则不需要。要有意识地从索引中去掉这样的列。
索引所基于的表用于事务处理还是主要用于查询？
如果它是事务处理的表，则需要确定由于给出附加的索引对事务处理的潜在的负面影响。在较好的查询性能和事务处理时的负面影响之间怎样权衡？如果它是主要用于查询的表，则最好建立索引。
是建立单列索引与组合索引？
如果了解应用程序中经常使用的列，则建立一个组合索引比分别建立单个索引更好。用索引优化SQL
- 99. 索引不当的例子 DELETE FROM HTJS.RZ_WLRZ_FPDKL_MX WHERE NSRSBH = ‘440301192193934’
AND
(FPDM = '3300043140' AND FPHM = '02436911‘
OR FPMW ='>46474>50*5>-700>5>1/188-77171**/4832+3988976<5-656-/*/5/84*<708+987<7>>0-74+58<>>84');
-- RZ_WLRZ_FPDKL_MX主键索引为FPID、NSRSBH用索引优化SQL
- 100. 该例的执行计划用索引优化SQL
- 101. 索引不当的例子 SELECT
*
FROM
FX_QY_NSRXX
WHERE
NSRSBH = :b1 AND FX_ZT = ‘1’
;
--PK_FX_QY_NSRXX(SWJG_DM,NSRSBH);
--IDX_FX_QY_NSRXX_SWJG_DM_CSSJ;用索引优化SQL
- 102. 该例的执行计划用索引优化SQL
- 103. 通过添加索引来消除排序消除排序的最好的方法之一就是添加索引。当运行在first_rows模式时，Oracle总是会用排序操作场所中的索引来分析order by子句。
有时，Oracle会用全索引扫描来代替磁盘排序操作。Oracle优化器会考虑查询返回的记录数目，而且会在排序成本与通过索引检索记录的成本之间做出权衡。在大多数情况下，Oracle只会在有大量的记录将被检索出来时考虑全索引扫描，而且要满足这个查询还必须进行一次磁盘排序。用索引优化SQL
- 104. 推荐使用索引的两个原因
提高从数据表中检索少量的数据集的速度。
预先排序结果集，这样SQL的order by等子句就不会引发内部排序了。 用索引优化SQL
- 105. 使索引无效的情况在where子句中执行一个函数会使索引失效。
使用不相等运算符也会使索引失效
like参数使用了通配符%指定了高层项
在使用RBO时，位图索引和基于函数的索引都会被忽略。用索引优化SQL
- 106. 特殊类型的B树索引位图索引
过去认为位图索引仅仅适用于非常少量的惟一数据值的数据列（少于50个数据值），现在位图索引功能已增强，最近的研究表明这一数据值已扩大到1000了。
基于函数的索引－BIF
反向（主）键索引
如果主键的值是按照上升的顺序生成的，那么所有的插入操作都需要改变B树中高一级的节点的数据块。
通常反主键索引可以用于合成主键索引中。用索引优化SQL
- 107. 带有IN条件查询的用法 SQL优化器常常会执行一个全表扫描，但可能会有许多不同的执行计划。
select
ename
from
emp
where
job IN
(‘manager’,’presedent’)
;select /*+ first_rows */
Ename
from
Emp
where
Job=‘manager’
union all
select
Ename
from
Emp
where
job=’presedent’; 用索引优化SQL
- 108. 表连接类型（1）等连接
两个表中的一对记录通过一个公共字段的匹配连接。
外部连接
是一个保存不完整记录的连接，两个表之间不存在匹配条件。
自连接
表和自身连接的特殊情况。
反连接
NOT IN或NOT EXISTS子句的子查询中会用到。
半连接
返回满足EXISTS子句的查询记录，即使条件右边有多条记录满足子查询中条件，该连接也不会复制谓词左边的记录。优化表连接
- 109. 表连接类型（2）等连接
在任何一个引用了两个或两个以上表的SQL语句中，在where子句中使用等式为表指定连接条件。
Oracle为等连接提供了三种连接方式，嵌套循环连接方式、散列连接方式和排序合并连接方式。 优化表连接
- 110. 表连接类型（3）外部连接
查询结果中将包含表中不匹配的字段。外部连接是通过在where子句的等式谓词中放置一个加号（+）来实现的。
Select
Emp.ename,
Bonus.comm.
From
Emp,
Bonus
Where
Emp.ename=bonus.ename(+) ；优化表连接
- 111. 表连接类型（4）反连接
子查询中找到的任何记录都不包含在结果集中的子查询。
select /* first_rows */
ename,
deptno
from emp
where
ename not in
(select
ename
from
bonus
);
－－where emp.ename=bonus.ename(+) and bonus.comm is null代替优化表连接
- 112. 表连接类型（4）-续 反连接提示
表反连接提示
表反连接提示merge_aj和hash_aj有助于提高反连接的效果。在使用时通常要求NOT IN子句中的字段有非空限制！可以在很大程序上提高NOT IN查询的性能。
合并反连接
是在NOT IN子查询执行的，以完成一个反连接，其中这个反连接倾向于使用全表扫描，而不是索引访问。
注：尽量不要使用NOT IN子句（它调用子查询），并且尽量使用NOT EXISTS
优化表连接
- 113. 表连接类型（5）半连接
Select /* first_rows */
dname
From dept
Where
exists
(select
*
from
emp
where
dept.deptno=emp.deptno
and
emp.comm>5000
) ；半连接的替代形式：
Select
distinct /*+ first_rows */
Dname
From
Dept,
Emp
Where
Dept.deptno=emp.deptno
And
Emp.comm>5000 ；
优化表连接
- 114. 表连接方法(1)嵌套循环连接
散列连接
排序合并连接
星型连接
优化表连接
- 115. 表连接方法(2) 嵌套循环连接
嵌套循环连接是最古老最基本的表连接方式，通常通过比较外部表的每一个主键和内部表的每一个主键进行操作。一般只有被连接表中至少有一个表支持索引的情况下，才考虑使用这种连接方式。
在使用CBO时，嵌套循环连接可以利用表大小的差异，通常选择较小的表作为驱动表。如果在RBO时，from子句中最后一个表作为驱动表，以确保最小的表作为嵌套循环查询的驱动表。 select /*+ rule*/
ename,
dname
from
emp,
dept
where
emp.deptno=
dept.deptno
and
emp.deptno=10; 优化表连接
- 116. 表连接方法(3) 散列连接
通过将驱动表加载进RAM，并使用散列技术以连接到第二个表，提高了等连接的速度.
如果表的大小不同，并且小表的大小接近hash_area_size中的可用内存时，推荐使用散列连接。
use_hash提示将对指定表执行散列连接。 select /*+ ordered use_hash(e,b) parallel(e,4) parallel(b,4) */
e.ename,
hiredate,
b.comm.
From
bonus b,
emp e
where e.ename=b.ename ;优化表连接
- 117. 表连接方法(4)排序合并连接
最简单最古老的表连接方式之一。排序合并连接不使用索引，对两个表的访问都是通过全表扫描实现的。在全表扫描进行后，来自每个扫描的结果集进行独立排序，然后，经过排序的结果集合并为查询结果集。select /*+ first_rows*/
ename,
dname
From
DEPT,
EMP
where
emp.deptno=dept.deptno
and
emp.deptno=10; 优化表连接
- 118. 表连接方法(4)-续 在下列情况下才会调用排序合并连接：
1)在连接表字段中不存在可用索引
2)查询将返回两个表中大部分的数据块
3)CBO认为全表扫描比索引扫描执行得快
use_merge提示会强制调用排序合并操作，排序合并操作通常与并行查询结合使用。
如果两个表的deptno字段添加索引后，且两个表都中包含少量的记录，CBO会探测到较低的基数，并且调用排序合并连接。select /*+ use_merge(e,b)
parallel(e,2）
parallel(b,2) */
E.ENAME,
B.COMM
from
EMP e,
BONUS b
where
e.ENAME=b.ENAME
and
emp.deptno=10;优化表连接
- 119. 表连接方法(5) 星型连接
星型连接的设计来源于数据仓库设计。是一种反标准化的设计！
星型方案的核心是一个事实表，这个字段和记录数目众多的表通常由主键和原事实组成。
任何一个基于星型方案的查询都将需要调用许多大表的连接—包括一个大的“事实”表和许多更小的引用表
传统方式的连接要求首先将最小的引用表同事实表连接，然后将其他的引用表连接到中间表。优化表连接
- 120. 评估表的连接顺序SQL解析中一个最耗时的步骤，就是决定大型N维表连接的连接顺序。在评估表连接顺序时，CBO将创建决策树。一个7维表连接中，评估7!种可能的表连接组合，对于每一个可能的表连接组合，Oracle将根据CBO统计资料，进行成本评估。
最好的解决办法是手工调整查询，过程是对from子句中的表进行重新排列，并使用ordered提示指导CBO按照from子句中指定的顺序进行表连接。优化表连接
- 121. 临时表与Oracle的版本对于特定类型的SQL操作(如聚集查询)来说，创建中间结果的数据表可以极好地提高性能。
create table as select …，简称CTAS。
如果使用Oracle8i以及更高版本，就不必删除和创建自已的临时数据表，而可以使用全局的临时数据表！用临时表调整SQL
- 122. 全局的临时数据表事物型临时表
在事务期间数据存在，事务结束后数据被自动删除。
会话型临时表
在会话期间数据存在，会话结束后数据被自动删除。用临时表调整SQL
- 123. 建立全局临时表的语法事物型临时表
create global temporary table tablename …
on commit delete rows ;
会话型临时表
create global temporary table tablename …
on commit preserve rows ;用临时表调整SQL
- 124. 与字典表一起使用CTASselect
username
from
dba_users
where
username not in
(select grantee
from
dba_role_privs
) ; create table temp1
as select
username
from dba_users;
create table temp2
as
select distinct
grantee
from
dba_role_privs;
select
username
from
temp1
where
username not in
(select grantee from temp2); 用临时表调整SQL
- 125. 用临时数据表调整聚集查询(1) 对于需要从单个数据表中选择聚集信息（如sum,avg等函数）的查询来说，使用临时数据表是一种非常有效地提高查询速度的机制，尤其适用于时间序列数据表。用临时表调整SQL
- 126. 用临时数据表调整聚集查询(2)select distinct
to_char(old_size.snap_time,’yyyy-mm-dd’),
sum(old_size.bytes),
sum(new_size.bytes),
sum(old_size.bytes)- Sum(new_size.bytes)
from
Stats$tab_stats old_size,
Stats$tab_stats new_size
where
New_size.snap_time=
(select max(snap_time)
from stats$tab_stats)
and
old_size.snap_time=
(select max(snap_time)-7 from stats$tab_stats)
group by
to_char(old_size.snap_time,’yyyy-mm-dd’) ;用临时表调整SQL
- 127. 使用子查询的基本知识只要可能的话，在SQL中无论何时都应该避免使用子查询。
在有些情况下，可以用一个标准的连接操作来代替。
在必须使用子查询的时候，可以选择相关的或非关联子查询。
可以选择使用IN子句或者EXIST子句作为查询的比较条件，一般情况EXIST优于IN。
反连接子查询是使用了NOT EXISTS或者NOT IN子句的子查询。
主查询也称外部查询，子查询也称内部查询。SQL子查询的优化
- 128. 子查询的例子相关子查询的例子
select
STUFF
From
TABLENAME
WHERE
KEY exists
--correlated subquery
(select
OTHER_STUFF
From
INNER_TABLE
Where
TABLENAME.KEY=INNER_TABLE.KEY)
; 非相关子查询的例子
select
STUFF
From
TABLENAME
WHERE
KEY in
--correlated subquery
(select
OTHER_STUFF
From
INNER_TABLE
）
； SQL子查询的优化
- 129. 相关和非相关子查询 一个“相关的”的子查询在子查询中引用的是外部数据表，而非相关子查询不会引用外部的数据表。对于从父查询中处理的记录来说，一个相关子查询是每行计算一次，然而一个非相关子查询只会执行一次,而结果集被保存在内存中，或者临时数据段中。 SQL子查询的优化
- 130. 如何选择相关或非相关子查询相关子查询的系统开销
对于返回到外层查询的记录来说，子查询会每行执行一次。因此，必须保证任何可能的时候子查询都要使用索引。
非相关子查询的系统开销
子查询只会执行一次，而且结果集通常是排序好的，返回的每一个记录在返回时都会被父级的查询所引用。
依赖于父查询和子查询本身所返回记录的期望数目
如果父查询返回较少的记录，那么再次执行子查询的系统开销并不会非常大；如果子查询只返回较少的记录，那么为内存保存结果集的开销也不会非常大。 SQL子查询的优化
- 131. 子查询执行的基本特征 在使用一个相关子查询时，使用IN子句或者EXISTS子句的子查询的执行计划通常相同。
EXISTS通常不适合非相关子查询。
在外部查询返回相对较少的记录时，相关子查询比非相关子查询执行得要更快。
在子查询返回不只一行数据时，查询不可能自动地转换为一个连接操作。
如果在内部查询中只有少量的记录，则非相关子查询将会比相关子查询执行得更快。 SQL子查询的优化
- 132. 子查询的自动SQL转换 子查询转换为标准的连接操作的条件：
1：使用IN子句的非关相关查询
2：使用EXISTS子句的相关子查询
有很多其他因素支配着SQL优化器何时将一个子查询自动转换为一个连接操作。首先，连接操作的两个数据表列通常都应该有惟一的数据索引。
不要总是期望Oracle会重写那些畸形的SQL语句，在任何可能的时候，应该自己来重写这些子查询。 SQL子查询的优化
- 133. 使用IN子句的非相关子查询Oracle并不能总是为转换查询选择最合适的驱动数据表，用ordered提示指定查询的驱动表会得到快得多的执行性能。
数据列惟一性和子查询转换：
当子查询中的from子句中有多个数据表时，只有下面两条中任何一条为真时，才可以确信惟一性。
在整个层次结构中最底层数据表上定义惟一主键的数据列存在了子查询的select列表之中。
至少有一个定义了惟一主键的数据列在select列表中，而且定义惟一主键的其他数据列都必须有指定的相等标准。SQL子查询的优化
- 134. 带有IN子句的相关子查询select /*+ first_rows */
ename,hiredate
from
emp,
where
deptno in
(select deptno
from dept
where emp.deptno=dept.deptno
); SQL子查询的优化
- 135. 使用EXISTS子句的相关子查询自动转换 限制条件：
相关子查询必须使用EXISTS子句。
外部的查询不能也是一个子查询(如嵌套的子查询)。
内部查询中判断条件必须使用相等运算符。
子查询不能包含group by或者connect by引用。
子查询中的相等运算符必须只返回单一记录。select
ename
from
emp e
where EXISTS
(select null
from bad_credit b
where
e.empno=b.empno
); SQL子查询的优化
- 136. 优化带有NOT IN的子查询select /*+ rule */
ename
from
emp e
where
empno NOT IN
(select empno
from bad_credit b
where
b.bad_credit_date>
sysdate-365
); select /*+ rule */
ename
from
emp e
where
empno IN
(select empno
from employees
MINUS
select empno
from bad_credit b
where b.bad_credit_date>
sysdate-365
); SQL子查询的优化
- 137. 优化带有NOT EXISTS的子查询select
ename
from
emp
where NOT EXISTS
(select
NULL
From
Dependents
Where
Emp.empno=dependents.empno
And
Relation=’son’
); select distinct
ename
from
emp,
dependents
where
emp.empno=dependents.empno(+)
and
relation(+)=’son’
and
dependents.empno is null
; SQL子查询的优化
- 138. 在子查询中使用ALL和ANY子句Select
Ename
from
emp
where
birthdate > ANY
(select
birthdate
from
customer
where
birthdate>’31-DEC-1998’
)
; select
ename
from
emp,
(select min(birthdate)
min_bday
from
customer
where
birthdate>’31-DEC-1998’
) in_view
where
emp.birthdate>in_view.min_bday
; SQL子查询的优化
- 139. 在Oracle查询中使用like子句 查询匹配前端是具体值时，查询利用索引范围扫描。
select
ename
from
emp
where
ename like ‘S%’; 前端是通配符时，则不能利用索引—全索引扫描或全表扫描。
select
ename
from
emp
where
ename like ‘%S%’; 使用内置函数和特殊运算符的SQL语句
- 140. 用case语句合并多重扫描select count(*)
from emp
where sal<1000;
select count(*)
from emp
where
sal between 1000 and 5000;
select count(*)
from emp
where sal>5000; select
count(case when sal<1000
then 1 else null end) poor,
count(case when sal between
1000 and 500 then 1 else
null end) count_blue_collar,
count(case when sal>5000
then 1 else null end) count_high
from emp ; 使用内置函数和特殊运算符的SQL语句
- 141. 用case语优化带有BIF的SQL语句 一般会在下面的情况中使用BIF：
转换字符
to_number、to_date、upper、lower、substr等BIF用来在检索的时候转换字符。
转换日期
to_char在转换Oracle的日期数据类型时非常有用。 使用内置函数和特殊运算符的SQL语句
- 142. BIF与字符数据类型一起使用转换字符,以去除其对字母大小写的敏感性。
如：
select
customer_stuff
from
customer
where
upper(last_name)=’JONES’
; 使用内置函数和特殊运算符的SQL语句
- 143. 使用substr BIFcreate index
emp_ename_substr
on
emp(substr,1,2)
;
Select
ename
from
emp
where
substr(ename,1,2)=’SM’ ; 使用内置函数和特殊运算符的SQL语句
- 144. BIF与日期数据类型一起使用select
ename
from
emp
where To_char(hireddate,’mon’)=’JAN’
; --执行的是全表扫描 Sql>alter session set
nls_date_format=’MM’;
select
ENAME
from
EMP
where
HIREDATE=’01’
;--执行的是索引范围扫描 使用内置函数和特殊运算符的SQL语句
- 145. 欢迎提出宝贵意见谢谢大家Wanghengfeng@aero-info.com.cn
