第五章 sql书写和索引创建.ppt

Oracle sql调整和索引创建 邱玉利 内容 Oracle Sql调整 Oracle 索引创建 优化器模式 Rule 将忽略开销和统计数据并完全基于基本数据字典信息生成执行计划总是选择oracle内部“最优的索引”，有可能选择并非理想的索引。 总是使用索引，总是从驱动表开始，在不可避免的情况下使用全表扫描，任何索引都可以。 Choose 选择最合适的优化器目标，根据统计数字有否，如果统计数据存在，将根据索引存在与否。这种选择模式存在很大的缺点。例如：如果多个表中只有一个表通过分析有详细的统计资料，那么，数据库对其他表会自己进行估算，这样，产生的执行计划不一定是准确的执行计划。 优化器模式（续） first_rows 基于开销的优化模式，以最快的速度返回记录，但是会造成总体查询速度的下降或是消耗更多的资源，通常选择完全索引扫描，而不是全表扫描，倾向于索引扫描。 All_rows 确保总体查询时间最短，但是可能收到第一条记录的时间更长，通常选择并行全表扫描，最适用于批量。 索引访问方式 索引范围扫描：对建有索引的列进行范围的扫描。例如： select * from table_name where coln and colb 单个索引扫描:对建有索引的列选择唯一值。例如： select * from table_name where col=n 快速全索引扫描:对建有索引的列通过索引进行全扫描。 例如：select count(col) from table_name, col列建了索引。 连接操作 嵌套循环连接(nl) 关系到两个表：一个小的内部表和一个外部表，oracle比较内部数据的每一条记录和外部数据集的每一条记录，并返回符合条件的记录集，常用在内部表存在索引的情况，必须确保正确的驱动表和正确驱动数据集。在很多情况下反应最快，尤其中间结果集比较小的情况下。 散列连接(hash) 指两个表中较小的那个表（驱动表）中执行全表扫描，然后再在内存中创建一个散列表，散列表用来从比较大的那个表中读取记录（hash_multiblock_io_count) 连接操作（续） 排序合并连接（merge) 是指从目标表中读取两个记录集，并使用连接字段将两个记录集分别排序。合并过程将来自一个数据集的每一条记录同来自另一个数据集于只相配的记录连接，并返回数据集的交集 对sql结果进行排序 Order by Union Group by Select distinct Select unique Creat index Sql的调整过程 定位高频使用的sql语句 调整sql 添加索引 更改优化器模式 添加提示 将调整持久化 Sql的调整目标 去掉不必要的大型表的全表扫描 缓存小型表的全表扫描 检验优化索引的使用 检验优化的连接技术 理解sql执行 1.解析 检查安全性：检验用户的权限同表进行比较 检查sql语法：书写符合正确的格式，语法是否正确。 理解sql执行（续） 2.执行 创建执行计划：产生器接受经过解析的sql,传递给sql优化器，优化器检查sql以及数据字典，然后产生执行计划 捆绑执行计划：扫描捆绑变量，然后为每个变量付值。 执行执行计划：将解析树放入缓存，然后执行所有的必要的磁盘输入输出 读取结果集：读取记录 理解sql执行（续） 3.显示结果集和排序结果集 4.转换字段数据：对那些经过内置函数进行过转换的字段数据进行重新格式化处理和转换 对表的处理 确定高频使用的表和索引 指定要存储的表 确定要进行记录重新排序的表 释放未被使用的索引 通过添加新索引来去掉全表扫描 sql进行全表扫描 字段不平衡索引分布 使用choose或all_rows 带有like的条件查询，开始用% 内置函数是索引无效，to_char、substr、decode。 使用并行提示 调整sql语句 添加提示：添加sql提示会修改执行计划，一般要尝试着几种都测试一下，寻找最合适的。 索引：添加索引能够去掉全表扫描，但是添加索引可能更改其他用到这个表的sql语句，因此添加索引的时候要注意不要影响其他的sql。 重新书写 Sql提示 优化器提示：对数据库的执行计划进行稳定 rule First_rows All_rows Sql提示（续） 表连接提示 use_hash:where 子句中的第一个表（最小的表），初始化参数，驱动表，分析统计资料，内存区。 use_merge:通常与并行查询使用 Use_nl:很少用，因为一般的都是用这个 Sql提示（续） 表反连接提示（not in ,not exists) merge_aj:在