SQL优化总结1

ORACLE 优化手段:
指执行SQL时绑定变量，减少软解析时间。
result cache 将某更新不频繁的表的结果中存在缓存中，下次直接读缓存,如果改表有更新，缓存自动失效
表联合查询时，9I前大表（结果集），小表（结果集）的位置
block块的大小的设置，设置小，则IO频繁，CUP低耗，反之反之
索引：主外键索引，分区表中有全局索引和局部索引，位图索引
分区表：有大表时，通过分区条件在小范围内查询
临时表:在事物或session结束后，系统自动清理数据
直接路径插入，不进行REDO，即不经过logbuffer插入很快，但第一次读即物理读，所以第一次读慢


高不位线
keep
count（字段） 不统计该了段为null的记录
count 列名或常数，与*性能完全一样。count(列)列越靠前，越快
表中的列，越靠前，访问越快


聚合因子，将有序的列作索引，否则索引无序，索引的位置很离散（索引回表的数据聚合度就低，找得慢）


联合索引，两个条件分别能查出很多，但交集少，就建立联合索引
          联合索引两个列中，如果一个列加了等值条件。等值条件放前面


max min 分开成两个SQL查询，能走索引，放在一条SQL不能走索引
        因为max,min在一条SQL，不能同时看左边和右




ID>=20 and ID<=21 
ID in (20,21) 后者效率高，因为oracle还会匹配20到21之前的小数
优化器以为前者这个区间内数据据多，所以不走索引


在执行计划上看：
unique索引:index unique scan 唯一性扫描，扫到后就停住
普通索引: index range scan 扫到后还要判断下一个符不符合，会多扫一次
unique索引稍稍能提升一点点


table access by user rowid
rowid是最快的方式，性能之王
优化办法，把常用的行的rowid存在一个变量中，下次直接使用




index full scan 全表扫描，一次读取一个块
select * from t order by id;


index fast full scan 快速全表扫描，一次读取多个块
select count(*) from t;


所以快速全扫描更快，因为其一次读取多个块，不能保证有序


大多情况下，
聚合函数都是快速扫描


index full scan (min/max) 前面已经提到，直接在索引表的开始的末端取数，很快








建索引的负面影响：


反向索引 减少热块竞争,索引离散存储,where 范围查询无法走索引


默认情况下，建索引会引起全表锁
但是online建索引时，索引建立的动作会自动等更新结束后进行,更新不会受到阻塞


建立索引会发生全表排序，
排序是一个很消耗性能的动作，典型地基于吞吐量的动作


索引字段，最好按什么类型存什么数据，否则发生数据类型转换，即查询时条件字段或调用类型转换函数，导致索引失效
对索引列进行运算，会引起索引逻辑失效，引起全表扫描






索引物理失效的原因：
LONG修改为CLOG时，会导致其他列的索引失效
对表进行move操作导致索引失效
(对大表delete大量数据后，查询依然很慢，是高水平位没释放，所以经常有人通过alter table move的方式来降低高水位,这时却导致索引物理失效)需要rebuil索引


对于分区表,
truncate分区   会导致全局索引物理失效、  不会导致局部索引失效  增加update global indexes,则全局索引不会失效
drop分区       会导致全局索引物理失效、  这时已经删除了，就没了分区索引了 增加update global indexes,则全局索引不会失效
split分区      会导致全局索引物理失效、  会导致局部索引失效   增加update global indexes,则全局索引不会失效
add分区        不会导致全局索引物理失效、不会导致局部索引失效
exchange       会导致全局索引失效,       不会导致局部索引失效  增加update global indexes,则全局索引不会失效


这几个操作，对于全局索引，只要用到update global indexes都不会失效




设置索引并行属性导致px coordinator




不要在循环内commit




like % 会不会走过引不确定，也可能是局部扫描


move外键所在的列，导致外键的表的索引失效，导致 主外键列更新阻塞




反向索引，避免热块，但是范围查询时，走不了索引


drop表后，闪回操作
flashback table t to before drop
这时索引其实都丢了，得重新建索引,约束也要重建


函数索引
reverse(列)结合 like '%ljb' 使like走部分索引
(col1 desc,col2 asc) 这样的索引可以避免 sort操作


虚拟索引，用来先测试看执行计划会不会用索引，如果不会用，就不建这个索引
因为当数据量大的时候，建立索引代价大，在不确定索引是否起效时，不要随意建，所以用虚拟索引先实验一下






位图索引，把某个表的所有行的某个列（该的取值范围很小，比如说只有0和1）的值相加，就是这个表的总行，所以count操作会很快
位图索引应用场合，重复度很高的列，并且很少改动的列


函数索引，就是把某个列通过函数计算的结果存索引表中


全文检索索引:基于ORACLE的lexer全文检索算法
            基于语义查询'高兴'，‘高’都能快速查询
            但‘兴’查不到结果，这是一个陷阱,因为它基于语义分析的算法
            其次，在更新操作时，还要执行同步命令才能更新成功
            同步语法：exec ctx_dd1.syncindex('列名','列值');


对于判断一个表是不是空的需求的优化，select * from t where rownum=1判断这个结果是不是空






特殊的函数索引
create index id_status on  (Case when status='N' then 'N' end);
总记录很多，但记录里id_status为N的情况很少
当查询为N的记录，会走索引


函数索引，函数内容变了，索引一定发重建，否则查询结果要能是错的




表的连接方式
如果返回结果数据量很大用hash join 或 merge sort join
反之用nested loop join
hash join 不支持不等值条件
只要是不等值条件就一定走nested join
 


starts即表访问次数如果很多，还是NESTE LOOP JOIN 则需要注意执行计划是否真实




搜集统计信息，用于优化器生成最优的执行计划


设置statistics_level=all
oracle优化器会搜集表的访问次数，即starts




统计信息搜集表
last_analyzed表是上次搜集的时间


第一次查询SQL后, 会出现
dynamic sampling used for this statement (level=2)
动态采样被执行


取消动态采样的SQL
select /*+ dynamtic_sampling(t 0)*/ * from t where ..


建索引后，也会自动搜集统计信息


获取执行计划的6种方式
select * from table(dbms_xplan.display());


statistics_level=all


（预估，不准）explain plan for + 你的SQL


(真实)set autotrace on


通过SQL_ID获得执行
v$sql视图中获取sql_id
select * from t(dbms_xplan.display cursor('sql_id'));






绑定变量会使ORAFLE生成不准的执行计划
where t.id< var :990
where t.id< var : 9
第一次sql执行后，优化器就会一直走全表扫描，因为它以为一直是返回大数据量的结果
实事上第二次只返回少量数据，应该走索引