SQL优化之优化器

RBO 所有规则为一组内置的规则，这些规则是硬编码在oracle数据库代码中的
具体有规则：oracle在代码里事先给各个类型的执行路径定了一个级别，一共15级，oracle认为等级值低的比等级值高的执行效率要高；
当有两条以上执行路径等级相同时，可以通过调整相关对象在数据字典中的缓存顺序来影响RBO对于执行计划的选择；RBO按照从右到左的顺序来决定谁是驱动表谁是被驱动表
执行计划一旦出现问题，很难对其做调整（不能使用索引）一般是等价改写sql；目标sql涉及的各个对象的顺序可能对sql执行计划有影响；oracle很多好的特性不支持RBO
不支持CBO的情况：
目标sql中涉及的对象有IOT或者分区表；使用了并行查询或者并行DML；使用了星形连接或者hash连接；使用了索引快速扫描；使用了函数索引等等
create table emp_temp as select * from emp;
 create index idx_mgr_temp on emp_temp(mgr);
 create index idx_deptno_temp on emp_temp(deptno);
alter session set optimizer_mode='RULE';
set autotrace traceonly explain
 select * from emp_temp where mgr>100 and deptno>100;
------------------------------------------------------------
CRB 所有的判断原则为成本，选择成本最小的执行计划，各执行计划的成本是根据目标sql语句涉及的表、索引、列等相关统计信息计算出来的
概念：
集的势/cardinality   集合所包含的记录数（其实就是结果集的行数）   值越大所对应的成本值往往也越大
可选择率/selectivity   施加指定谓词条件后返回的结果集的记录占未施加任何谓词条件的原始结果集的记录数的比率 可选择率越大返回的结果集就越大，估算出来的成本也就越大
可传递性  对目标sql做简单的等价改写，提供更多的执行路径。增肌得到高效执行计划的可能性
CBO的局限性：
1、默认sql语句的where条件的列之间是独立的没有关联关系——可使用动态采样的准确性或者多列统计信息规避；但是动态采样的准确性取决于采样数据的质量和采样数据的数量；而多列统计信息
不适用与有关联关系的情形；
2、假设所有的目标sql都是单独执行的并且互不干扰   ————没有考虑缓存的影响
3、CBO对直方图统计信息有限制:
a.12c之前频率直方图对于的桶不能超过254，超过就会使用平衡直方图（统计不精确）
b.对文本型字段文本值的头32字节取出来做统计信息存储在数据字典中
4、在解析多表关联的目标sql时，可能会漏选正确的执行计划
------------------------------------------------------------------
优化器的模式：
1、RULE  表示使用RBO来解析目标sql
2、CHOOSE     目标sql所涉及的表均没有统计信息就使用RBO，只要所涉及的目标sql的表有一个有统计信息就使用CBO
3、FIRST_ROW_n   以最快的响应速度返回头n条记录（此执行计划的成本值会被修改一个很小的值）
4、FIRST_ROWS  联合使用RBO和CBO，在特定的情况下使用RBO（比如使用索引来避免排序）
5、ALL_ROWS   使用CBO来解析sql 侧重点在最佳的吞吐量
---------------------------------------------------------
访问数据的方法
1、访问表的方法             
a.全表扫描  执行时间不稳定、不可控随数据量递增而增加
b.ROWID 扫描（用户输入rowid或者访问索引得到的rowid）
2、访问索引的方法    B树索引是自平衡的，走索引访问数据的时间是可控的、基本稳定的、这也是走索引和全表扫描的最大区别
a.索引唯一性扫描 适用于where条件是等值查询的目标sql，扫描的对象时唯一索引
b.索引范围扫描 适用于所有的B树索引
c.索引全扫描 适用于所有B树索引 执行结果是有序的并且是按照索引键值列来排序的 不需要回表  （前提条件是目标索引至少有一个索引键值列的属性是not null）
d.索引快速全扫描 适用于所有B树索引
e.索引跳跃式扫描 适用于所有复合B树索引 where条件中没有对目标索引的前导列指定查询条件但同时又对该索引的非前导列指定查询条件的目标sql依然可以使用上该索引
 （相当于对目标sql做等价改写）仅适用于前导列的distinct值数量较少、后续非前导列的可选择性又非常好的情形
------------------索引全扫描月索引快速扫描的区别---------------------------
索引快速扫描只适用于CBO
索引快速扫描可以使用多块读，也可以并行执行
索引快速扫描的结果不一定是有序的（根据索引行在磁盘上的物理顺序来扫描）
-------------------------------
表连接
表连接的类型 ：1.内连接（自然连接的连接列是表连接的两个表所有的同名列）
 2.外连接
表连接的方法：
1、排序合并连接
优缺点及适用场景——
a.排序合并连接效率远不如hash连接，但排序合并连接使用范围广；hash连接只适用等值连接；
b.排序合并连接不适合OLTP系统，因为排序在OLTP系统非常昂贵；如果能避免排序还是适合于OLTP系统的，例如两表都有索引实际并不需要排序
2、嵌套循环连接
优缺点及适用场景——
a.适用于驱动表结果集小，被驱动表有索引（唯一索引或者选择性很好的非唯一性索引）
b.嵌套循环可以快速响应；排序合并要等到排完序后做合并操作时才能开始返回，hash连接要等到驱动结果集所对应的hash table全部建完后才能开始返回
c.向量IO实现了在单块读不降低的情况下减少这些单块读所需要耗费的物理IO数量，从而提高嵌套循环连接的执行效率
3、hash连接
优缺点级适用场景——
a.hash连接不一定会排序，或者说大多数情况下都不需要排序
b.hash连接的驱动结果集所对应的连接列的可选择性应尽可能好
c.hash连接只适用于CBO ，也只能适用于等值连接
d.hash连接很适合小表和大表直接做连接且连接结果集的记录较多的情形，特别是小表的连接列的可选择性非常好的情况下，执行时间可以近似看做是和全表扫描那个大表所耗费的时间相当
e.hash连接时 施加了目标sql中指定的谓词条件后得到的数据量小的结果集所对应的hash table 能够完全容纳在PGA工作区中，此时hash连接的执行效率会非常高
4、笛卡尔连接
优缺点及适用场景——
a.通常是因为目标sql漏写了表连接条件，笛卡尔积一般是不好的，出发刻意这么做
b.有时候是目标sql中使用了ORDERED HINT ，同时在该SQL的SQL文本中位置相邻的两个表之间有没有直接的关联条件
c.有时候是因为目标SQL中相关表的统计信息不准
-----------------------------------------------------------
反连接：适用谓词条件 not in、<> all 、not exists
半连接：适用谓词条件 in 、= any 、exists
星形连接： 适用于数据仓库
 
--------------------------------------------练习命令----------------------------------------------
create unique index idx_emp_temp on emp_temp(empno);
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP_TEMP',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);
exec dbms_stats.set_table_stats(ownname=>'SCOTT',tabname=>'EMP_TEMP',numrows=>10000000,no_invalidate=>false);
exec dbms_stats.set_index_stats(ownname=>'SCOTT',indname=>'IDX_MGR_TEMP',numlblks=>100000,no_invalidate=>false);
select empno,ename,rowid, dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid)||'_'||dbms_rowid.rowid_row_number(rowid) as loc from emp;
 select table_name,num_rows,blocks,to_char(last_analyzed,'yyyymmdd hh24:mi:ss') from dba_tables where table_name='EMP';
 select index_name,column_name,column_position from dba_ind_columns where table_name='EMP';
alter system flush shared_pool;
alter system flush buffer_cache;
alter table emp_test add constraint pk_emp_test primary key (empno,col1,col2,col3);
select /*+ index_ffs(emp_test pk_emp_test) */empno from emp_test;
select /*+ ordered use_nl(t2) */t1.c1,t1.c2,t2.c2 from t1,t2 where t1.c1=t2.c1;
select /*+ optimizer_features_enable('9.2.0') ordered use_nl(t2) */t1.c1,t1.c2,t2.c2 from t1,t2 where t1.c1=t2.c1;
SQL> set linesize 800
SQL> set pagesize 900
SQL> select * from t1 where not exists (select null from t2 where c1=t1.c1);
SQL> select * from t1 where c1 <> all(select c1 from t2);
SQL> select * from t1 where c1 not in (select c1 from t2);
------------------------
SQL> oradebug setmyid
oradebug event 10104 trace name context forever ,level 1
select /*+ ordered use_hash(t2) */t1.c1,t1.c2,t2.c2 from scott.t1,scott.t2 where t1.c1=t2.c1;
oradebug tracefile_name
-------------------------------