select语句的优化.pptVIP

select语句的优化 课程目标 回顾Oracle优化器 掌握使用索引的原则 对于select语句的一些建议 了解对查询语句进行分析的常用工具 回顾优化器 优化器介绍 优化器直接决定了语句的执行效率 优化器决定了SQL语句执行时候的系统开销 Oracle内部有自己的缺省工作机制 用户可以干预 优化器分类 基于规则的(RBO) 基于成本的(CBO) RBO Oracle内部有一系列缺省的access path 每个access path都有一个优先值 当SQL语句存在多个access path时选取小优先值的 基于内部规则的 RBO access path RBO Path 1: Single Row by Rowid RBO Path 2: Single Row by Cluster Join RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key RBO Path 4: Single Row by Unique or Primary Key RBO Path 5: Clustered Join RBO Path 6: Hash Cluster Key RBO Path 7: Indexed Cluster Key RBO Path 8: Composite Index RBO Path 9: Single-Column Indexes RBO Path 10: Bounded Range Search on Indexed Columns RBO Path 11: Unbounded Range Search on Indexed Columns RBO Path 12: Sort Merge Join RBO Path 13: MAX orMIN of Indexed Column RBO Path 14: ORDER BY on Indexed Column RBO Path 15: Full Table Scan CBO 对SQL语句评估每个潜在执行计划的开销 选取总的开销最小的执行计划 开销是对I/O，CPU等资源的评估，是个数值 基于开销的 优化器的设置 实例级别 会话级别（高于实例级别） SQL语句级别（高于会话级别） 实例级别 初始化参数optimizer_mode Choose Rule First_rows First_rows_n(1,10,100,1000) All_rows 会话级别 Alter session set optimizer_mode={choose|rule|first_rows|first_rows_n|all_rows} SQL语句级别 Hint Hint 一种人工干预优化器选取access path的方法 在SQL语句中强制设置 可以用来控制： 优化器的选取 Access path Join order … 优化器相关的Hint ALL_ROWS FIRST_ROWS(n) CHOOSE RULE Hint的语法 {DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */ Example SELECT /*+ ALL_ROWS */ empno, ename, sal, job FROM emp WHERE empno = 7566; SELECT /*+ RULE */ empno, ename, sal, job FROM emp WHERE empno = 7566; UT系统中使用的优化器 基于rule optimizer_mode设置为choose 不要对任何表/索引进行analyze 关于索引 索引的利与弊 提高查询语句的效率 减慢DML（增、删、改）语句的速度 索引与全表扫描 有索引，查询需要两次I/O 索引本身 数据本身 全表扫描就一次I/O 利用索引和全表扫描的权衡 全表扫描 表很小、记录排它性很小 利用索引 表很大、记录排它性很高、索引数据少 在哪些列上建立索引 在where子句中经常引用到的列 在join中引用到的列 在子表的FK上建立索引 防止对父表操作时锁住子表 在哪些列上不要建立索引 经常进行增、删、改的表 经常进行增、删、改的列 列的排它性小 索引选取的最高优先原则 如果是RBO 基于RBO access path 如果是CBO 基于cost 关于索引的其它(1) 索引不是万能药，不建议表上的索引超过5个 关于索引的其它(2) Where语句中存在两个以上可以利用的索引 如果它们都是单列索引oracle会一起用 如果它们不都是单列索引则oracle只会用其中一个（一般是只用多列索引，除非单列索引为P