How to Find SHOUG?

介绍 ORACLE SQL 语句执 行计划的访问路径 by SHOUG. 王朝阳

How to Find SHOUG?

Optimizer operators 1. 行源操作 1. Unary Operations: 一元运算, 即单表的查询 ; 2. Binary Operations: 二元运算, 两表的连接 ; 3. N-ary Operations: 多元运算 ; 2. 主要的结构和访问路径 : 1. 表 : 2. 索引 : 3. 索引的基本概念 : 1. Full Table Scan; 2. Rowid Scan: 很少使用, 多用在内部的某一个步骤 ; 3. Sample Table Scan: 很少使用 ; 1. Index Scan(Unique); 2. Index Scan(Range); 3. Index Scan(Full); 4. Index Scan(Fast Full); 5. Index Scan(Skip); 6. Index Scan(Index Join); 7. Using Bitmap Indexes; 8. Combining Bitmap Indexes; 1. B-Tree Indexes: 平衡树索引, 最常见的索引 ; 1. 正常索引 ;

2. 基于函数的索引 : 1. 创建函数索引相当于在表上添加一个伪列 ; 2. 查看定义 ;

3. IOT(Index-Organized Table): 将表结构整体放入索引中, 而且按照主键进行排序, 一定要有主键, 非主键的列一定要落在索引条目里 ; 4. Bitmap Indexes; 1. 可以索引空值 ; 2. 适当发生转换 :TO ROWIDS/FROM ROWIDS/COUNT; 3. 可以进行的操作 :MERGE/AND/OR/MINUS/KEY ITERATION, 位运算的速度很快 ; 4. 位图索引可以进行 SINGLE VALUE/ RANGE SCAN/ FULL SCAN 扫描 ; 5. 缺点是位图索引不能经常更新, 效率很差 ; 5. Cluster Indexes; 2. 索引的属性 : 1. 键压缩 ; 1. 如果要做两个表的关联查询则最少查询两个块 ; 2. CLUSTER 把两个表按照关联的字段把记录存放在同一个块上 ; 这样只用查一个块即可 ; 查找时效率提高一倍 ; 3. 用在总是关联查询两个表的情况, 一般是不用的 ;ORACLE 内部大量使用 ; 4. cluster 上的索引不能指定列, 必须使用所有的列 ; 5. 基于 cluster 的表没有 segment;

2. 反转键值 ( 考点 ): 可以防止索引块争用 (buffer busy wait), 只支持等式连接, 不支持范围扫描 ; 3. 顺序 / 倒序 ; 3. 索引和 NULL 值 : 1. NULL 值与索引的关系 : 1. 基于单列的唯一索引, 可以多次插入 NULL 值 (NULL <> NULL), 因为索引并不存储 NULL 值 ; 2. 基于多列的符合索引, 尽管全为 NULL 的值可以多次插入 ([NULL, NULL] <> [NULL, NULL]), 索引也不会存储, 但不全为 NULL 的重复行则不能重复插入,; 2. NULL 值与执行计划 : 1. 如果列的属性允许为 NULL, 条件为 IS NULL 的话, 肯定走全表扫描, 因为索引不保存 NULL 值 ; 2. 如果列的属性允许为 NULL, 条件为 IS NOT NULL 的话, 会走全索引扫描 ; 3. 如果列的属性为 NOT NULL, 条件为 IS [NOT] NULL 的话, 走索引扫描 ; 4. 组合索引的话, 如果条件中只出现一列的话跟单列索引一样 ; 5. 组合索引的话, 如果条件中出现两列, 会优先选择走索引 ; 3. IS NULL 使用索引的办法 : 4. 索引的管理 : 1. 在 NULL 的列上创建函数索引 :nvl(column_name, - 1), 查询的时候条件指定函数索引 : where nvl(column_name, -1) = -1; 2. 为 NULL 的列添加默认值 ; 1. 插入数据后再创建索引, 对于 DW 来言 ; 2. 在适当的表和列上加索引 ; 3. 注意组合索引的顺序 ;

4. 控制索引的数量 : 每添加一个索引,DML 的效率下降 3 倍, 官方推荐最多加 7 个索引 ; 5. 删除不使用的索引 ; 6. 为索引指定单独的表空间 ; 7. 创建索引时使用并行,NOLOGGING 参数 ; 8. COALESCING 是合并相邻叶子节点,rebuild 则可以减少索引树的高度 ; 5. 检测索引是否被使用了 : 1. 添加对某个索引的监控 :ALTER INDEX EMP_EMP_ID_PK MONITORING USAGE; 2. 查看监视的对象使用情况 :SELECT * FROM v$object_usage; 默认是没有任何的监视的 ; 3. 使用此索引后再查看 ; 4. 取消索引监控 :ALTER INDEX EMP_EMP_ID_PK NOMONITORING USAGE;

5. 缺点 : 每次只能添加一个索引, 而且不记录索引使用的次数 ; 6. 不使用索引的原因 : 1. 被检索的列上用了函数 ; 2. 数据类型不匹配 ; 发生隐士转换是转化左边的列, 而不是右边的列 ; 3. 统计信息是否最新 ; 4. 列是否是空值 ; 5. 索引效率太低 ; 4. 各种访问路径的原理及使用场景 : 1. Full Table Scan: 1. 会执行 Multiblock Reads, 参考初始化参数 :db_file_multiblock_read_count; 2. 会读取 HWM(High-Water Mark) 以下所有被格式化的块 ; 3. 过程中可能会过滤某些记录 ; 4. 用在要获得大量记录的时候, 比索引扫描更快 ; 5. 使用的场景 : 1. 没有合适的索引 ; 2. 过滤条件不好, 甚至是没有过滤条件 ;

3. 表太小, 记录数很少 ; 4. 需要并行扫描, 并行扫描一定不走索引, 如果确定是全表的话可以考虑并行 :SELECT /*+ PARALLEL(d 4) */ * FROM departments d; 5. 加全表扫描的 hint 时 :SELECT /*+ FULL (d)*/ * FROM departments d WHERE department_id = 10; 6. IS NULL 的操作 ;

2. ROWID Scan: 1. 根据记录的 rowid 查询, 最快的访问方式, 但不经常使用, 可能会出现在执行计划的某个步骤中 ; 2. 使用的方法 : 3. Sample Table Sacns: 基本不用,SELECT * FROM departments SAMPLE BLOCK (10) SEED (1);

4. Index Unique Scan: 条件中指定了主键列或者唯一键的列就走唯一键扫描 ; 5. Index Range Sacn: 1. 过滤的条件上不是主键 / 唯一索引, 就会走索引范围扫描 ; 2. 如果对有索引的列排倒序就会有索引倒序扫描 ;( 因为索引本身是排序的, 所以执行计划中不会有排序的步骤, 按照索引列排序效率会高 ;)

3. 走函数索引的例子, 也是索引范围扫描的一种 ; 6. Index Full Sacn vs Index Fast Full Sacn: 1. 出现的条件 : 1. SELECT 与 WHERE 子句出现的所有的列必须存在索引, 而且为非空列, 因为索引不存放 NULL 值 ; 2. 返回的数据总行占据索引的 10% 以上的比例 ; 2. Index Full Sacn: 1. 完全按照索引存储的顺序依次访问整个索引树, 当访问到叶子节点时, 按照双向链表方式读取相连的节点值 ; 2. 使用 Single Read, 会产生 db file sequential reads 事件 ; 3. 对于索引列上的排序, 总是会使用 Index Full Scan; 4. 索引列上 is not null 的操作, 会走全索引扫描 ; 3. Index Fast Full Sacn: 1. 对于索引的分支结构只是简单的获取, 然后扫描所有的叶节点, 导致索引结构没有访问, 获得的数据没有根据索引键的顺序排序, 读取效率高. 但是如果 SQL 语句中有排序操作的话, 还要额外多做一次排序 ; 2. 在使用 Index Fast Full Sacn 时, 使用 Multiblock Read, 会产生 db file scattered reads,db_file_multiblock_read_count 参数的设置很重要 ; 3. 统计行数, 如 count(*) 的操作总是会使用 Index [Fast] Full Scan 的 ;

4. 会使用大量的内存和 CPU 资源 ; 4. Index [Fast] Full Scan 的例子 ; 7. Index Skip Scan: 1. 创建了复合索引, 但是条件中只有复合索引中的第二列, 而且当第一列的 distinct 值不多时, 会发生跳跃扫描 ; 2. 创建一个测试表, 和一个联合索引, 当第一列可选值少而条件中只查找第二列时, 发生跳越扫描 ;

3. 如果第一列的可选值很多, 条件中查找第二列的话, 发生全表扫描 ; 8. Index Join Scan: 查询的列都不为空, 而且都有索引才会出现联合扫描 ; 9. AND-EQUAL 操作 : 两列都有索引, 分别扫描两列获得记录的 rowid, 然后再取 rowid 的交集 ; 10. Bitmap Index: 1. Bitmap 的单值扫描 ; 2. Bitmap 的范围扫描 ;

3. Bitmap 的迭代操作操作 ; 4. Bitmap 的 AND 操作 ; 5. 排序操作 : 1. Sort Operator:

1. AGGREGATE: 在 group 操作用会用到, 统计结果 ; 2. UNIQUE: 评估是否重复 ; 3. JOIN: 做合并操作 ; 4. GROUP BY,ORDER BY: 在 group by 和 order by 的时候使用 ; 2. Hash Operator: 1. GROUP BY: 在 group by 操作时使用 ; 2. UNIQUE: 跟 SORT UNIQUE 一样 ; 3. 10g 之后结果默认不排序, 如果想要排序后的结果, 应该总是使用 ORDER BY 字句 ; 6. Buffer Sort: 1. BUFFER SORT 不是一种排序, 而是一种临时表的创建方式 ; 2. BUFFER 表示在内存中存放了一张临时表 ; 3. SORT 来修饰 BUFFER 表示具体再内存的什么地方 : 在 PGA 的 SQL 工作区的排序区 ; 4. BUFFER SORT 的例子 : 7. INLIST ITERATOR: 1. 是由于 IN 操作引起的, 要关注迭代的次数, 一次迭代就要有一次访问, 如果没有索引可能性能问题会很严重 ;

2. 可以使用 UNION ALL 操作代替 ; 3. INLIST ITERATOR 的例子 ; 8. 视图的操作 : 1. Merge View: 是将 View 的定义和外部查询合并, 高效的方式 ; 2. No Merge View: 先将 View 的数据取出来再做外部条件的过滤, 效率低 ; 9. 执行计划中的 Count 和 Count Stopkey:oracle 数据库的优化关于 rownum 操作 ; 1. 在查询中有时使用到伪列 rownum, 对使用伪列 rownum 的查询, 优化器要么使用 count 操作, 要么使用 count stopkey 操作来对 rownum 计数器进行增量 ( 注意 : 这里的 count 操作和 count stopkey 操作与 count 函数没有任何关系 ). 如果对 rownum 伪列应用一个限定条件, 如 :where rownum<10, 则使用 count stopkey 操作 ; 如果不为 Rownum 伪列指定限定条件, 则是使用 count 操作 ;

2. 不在 Rownum 伪列上使用限定条件 :SELECT employee_id, ROWNUM FROM employees;(employee_id 是主键 ) 为了完成这个查询, 优化器执行一个全索引扫描 ( 主键索引 ), 后跟一个 count 操作生成每个行的 rownum 值,count 操作不需要等待得到整个记录集, 随着从 employee 表中返回记录,rownum 计数器进行增量, 从而确定每个记录的 rownum; 3. 在 rownum 伪列上使用一个限定 :SELECT employee_id, ROWNUM FROM employees WHERE ROWNUM < 10; 为了实施限定条件, 优化器用 count stopkey 操作代替 count 操作, 它将 rownum 伪列的增量值与限定条件中指定的值进行比较, 如果 rownum 伪列的值大于限定条件中指定的值, 则查询不再返回更多的行 ; 4. 在 where 子句中不能使用 rownum>10 这样的操作, 只能使用 rownum<10 这样的操作 ;

10. Min/Max and First Row 操作 : 当使用 MAX/MIN 函数时发生 ; 11. 连接的方式 : 1. 一个连接定义了两个行源的关系, 也是合并两个行源间数据的方法 ; 2. 主要由连接的谓词所控制, 定义了对象间的关系 ; 3. 连接的方法 : 1. Nested Loops: 1. 对于被连接的数据子集较小的情况, 嵌套循环是个较好的选择 ; 2. 返回第一条记录最快的方式 ; 3. 这种情况下, 内表被外表驱动, 外表返回的每一行都要在内表中检索找到它匹配的行, 因此整个查询返回的结果集不能太大 (eg:<1w); 4. 要把返回子集较小的表作为驱动表, 而且内标的连接字段上一定要有索引 ; 5. 使用 USE_NL(table_name1 table_name2) 可是强制 CBO 执行嵌套循环连接 ;

2. Sort-Merge Join: 1. 通常情况下散列连接的效果都比排序合并连接要好, 然而如果行源已经被排过序, 在执行排序合并连接时不需要再排序了, 这时排序合并连接的性能会优于散列连接 ; 2. 可以使用 USE_MERGE(table_name1 table_name2) 来强制使用排序合并连接 ; 3. Sort Merge join 使用的情况 : 3. Hash Join: 4. 连接方式的比较 : 1. 用在没有索引 ; 2. 数据已经排序的情况 ; 3. 不等价关联 ; 4. HASH_JOIN_ENABLED=FALSE; 1. 散列连接是 CBO 做大数据集连接时常用的方式, 优化器使用两个表中较小的表 ( 行源 ) 利用连接键在内存中建立散列表, 然后扫描较大的表并探测散列表, 找出与散列表匹配的行 ; 2. 这种方式适用于较小的表完全可以放于内存中的情况, 这样总成本就是访问两个表的成本之和, 但是在表很大的情况下并不能完全放入内存, 这时优化器会将它分割成若干不同的分区, 不能放入内存的部分就把该分区写入磁盘的临时段, 此时要有较大的临时段从而尽量提高 I/O 的性能 ; 3. 也可以用 USE_HASH(table_name1 table_name2) 提示来强制使用散列连接, 如果使用散列连接 HASH_AREA_SIZE 初始化参数必须足够的大, 如果是 10g 以后,Oracle 建议使用 SQL 工作区自动管理, 设置 WORKAREA_SIZE_POLICY 为 AUTO, 然后调整 PGA_AGGREGATE_TARGET 即可 ; 1. Hash join 的工作方式是将一个表 ( 通常是小一点的那个表 ) 做 hash 运算, 将列数据存储到 hash 列表中, 从另一个表中抽取记录, 做 hash 运算, 到 hash 列表中找到相应的值, 做匹配 ;

2. Nested loops 工作方式是从一张表中读取数据, 访问另一张表 ( 通常是索引 ) 来做匹配,nested loops 适用的场合是当一个关联表比较小的时候, 效率会更高 ; 3. Merge Join 是先将关联表的关联列各自做排序, 然后从各自的排序表中抽取数据, 到另一个排序表中做匹配, 因为 merge join 需要做更多的排序, 所以消耗的资源更多, 通常来讲, 能够使用 merge join 的地方,hash join 都可以发挥更好的性能,Merge Join 太消耗 PGA; 5. 连接的类型 : 12. 多行源的操作 1. [ 不 ] 等值连接和自然连接 ; 2. 外连接 : 全连接, 左外连接, 右外连接 ;( 外连接 :+ 号放那边, 哪边记录少 ;) 3. 半连接 :EXISTS 子句 ; 4. 反连接 :NOT IN 字句 ; 1. FILTER; 2. CONCATENATION; 3. UNION [ALL] 4. INTERSECT; 5. MINUS; -- Full Table Scan; SELECT * FROM departments WHERE manager_id = 100; SELECT /*+ PARALLEL(d 4) */ * FROM departments d; SELECT * FROM departments d WHERE department_id = 10; SELECT /*+ FULL (d)*/ * FROM departments d WHERE department_id = 10; -- ROWID Sacn; SELECT * FROM departments WHERE ROWID = 'AAAMiZAAFAAAAA4AAI'; SELECT * FROM departments WHERE ROWID = ( SELECT rowid FROM departments WHERE manager_id = 100);

-- 函数索引的例子 ; CREATE INDEX idx_employees_fun_firstname ON employees (upper(first_name)); SELECT * FROM user_tab_cols WHERE table_name = 'EMPLOYEES'; SELECT * FROM user_ind_expressions WHERE index_name = 'IDX_EMPLOYEES_FUN_FIRSTNAME'; -- Index Skip Scan 的例子 ; CREATE TABLE skip_test AS SELECT object_id, object_name, decode(object_type, 'VIEW', 'VIEW', 'TABLE') AS object_flag, object_type FROM dba_objects WHERE ROWNUM <= 3000; CREATE INDEX idx_skip_test ON skip_test(object_flag, object_id); EXEC dbms_stats.gather_table_stats(user, 'skip_test', CASCADE => TRUE); SELECT * FROM skip_test WHERE object_id = 100; -- 如果联合索引第一列的候选值太多, 则发生全表扫描 ; DROP INDEX idx_skip_test; CREATE INDEX idx_skip_test ON skip_test(object_type, object_id); EXEC dbms_stats.gather_table_stats(user, 'skip_test', CASCADE => TRUE); SELECT * FROM skip_test WHERE object_id=100; -- 位图索引的例子 ; CREATE TABLE bitmap_test AS SELECT ROWNUM rn, MOD(ROWNUM, 4) bit, CASE MOD(ROWNUM, 2) WHEN 0 THEN 'M' ELSE 'F' END gender FROM dual CONNECT BY ROWNUM < 1000; CREATE BITMAP INDEX bmp_bitmap_bit on bitmap_test (bit); CREATE BITMAP INDEX bmp_bitmap_gender on bitmap_test (gender); EXEC dbms_stats.gather_table_stats(user, 'bitmap_test', CASCADE => TRUE); ALTER SESSION optimizer_mode = 'FIRST_ROWS_1000'; SELECT * FROM bitmap_test WHERE bit = 3; SELECT * FROM bitmap_test WHERE bit > 2; SELECT * FROM bitmap_test WHERE bit IN (2, 3); SELECT * FROM bitmap_test WHERE bit IN (2, 3) AND gender = 'M';

作者个人简介 王朝阳, 网名 : 小 máo Oracle 技术爱好者与研究者目前是盛大网络高级 DBA, 获有 Oracle 10g/11g OCM Oracle 10g/11g OCP MCITP/MCTS DBA(MSSQL2008) RHCE Java Programmer 等认证 +5 年数据库使用及管理经验, 擅长异构数据库和不同操作系统之间数据的同步, 对此有深入研究, 实战经验丰富, 对 Oracle 技术有独到见解对数据库技术抱有极大热情, 乐于分享自我心得总结, 坚持撰写 Oracle 相关技术文章并发布个人博客个人网站 : www.royalwzy.com gmail/gtalk:sonne.k.wang@gmail.com LinkedIn: www.linkedin/in/wangzhaoyang