1. 行源操作
   1. Unary Operations:一元运算,即单表的查询;
   2. Binary Operations:二元运算,两表的连接;
   3. N-ary Operations:多元运算;
2. 主要的结构和访问路径:
   1. 表:
      1. Full Table Scan;
      2. Rowid Scan:很少使用,多用在内部的某一个步骤;
      3. Sample Table Scan:很少使用;
   2. 索引:
      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;
3. 索引的基本概念:
   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;
         1. 如果要做两个表的关联查询则最少查询两个块;
         2. CLUSTER把两个表按照关联的字段把记录存放在同一个块上;这样只用查一个块即可;查找时效率提高一倍;
         3. 用在总是关联查询两个表的情况,一般是不用的;ORACLE内部大量使用;
         4. cluster上的索引不能指定列,必须使用所有的列;
         5. 基于cluster的表没有segment;
   2. 索引的属性:
      1. 键压缩;
      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使用索引的办法:
         1. 在NULL的列上创建函数索引:nvl(column_name, -1),查询的时候条件指定函数索引: where nvl(column_name, -1) = -1;
         2. 为NULL的列添加默认值;
   4. 索引的管理:
      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使用的情况:
             1. 用在没有索引;
             2. 数据已经排序的情况;
             3. 不等价关联;
             4. HASH_JOIN_ENABLED=FALSE;
       3. Hash Join:
          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即可;
    4. 连接方式的比较:
       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. 连接的类型:
       1. [不]等值连接和自然连接;
       2. 外连接:全连接,左外连接,右外连接;(外连接:+号放那边,哪边记录少;)
       3. 半连接:EXISTS子句;
       4. 反连接:NOT IN字句;
12. 多行源的操作
    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’;