CBO访问路径

访问路径就是从数据库中检索数据的方式。通常来说，检索一个表中少量的数据行应该使用索引访问，但是检索大量数据时全表扫描可能优于索引。

    全表扫描（Full Table Scans）全表扫描将读取HWM之下的所有数据块，访问表中的所有行，每一行都要经WHERE子句判断是否满足检索条件。当Oracle执行全表扫描时会按顺序读取每个块且只读一次，因此如果能够一次读取多个数据块，可以提高扫描效率，初始化参数DB_FILE_MULTIBLOCK_READ_COUNT用来设置在一次I/O中可以读取数据块的最大数量。

 

    优化器何时会使用全表扫描?在以下情况中优化器会使用全表扫描：

1、无可用索引

如下面例子：

SELECT last_name, first_name FROM employees WHERE UPPER(last_name)='TOM'

    last_name字段有索引，但在查询中使用了函数，因此该查询不会使用索引。如果想让这个查询走索引，则需要建立函数索引create index ind_upper_lastname on last_name (upper(last_name))。

    特别要注意的是隐式转换，比如colx字段是varchar2型但存放数字：where colx=123456，这时会发生隐式转换TO_NUMBER(colx)，此时colx上的索引也会失效。

2、大量数据

    如果优化器认为查询将会访问表中绝大多数的数据块，此时就算索引是可用的也会使用全表扫描。

3、小表

    如果一个表HWM之下的数据块比DB_FILE_MULTIBLOCK_READ_COUNT要少，只需要一次I/O就能扫完，则使用全表扫描要比使用索引的成本低，此时会使用全表扫描。如果有这样小表访问频率又高，通常把它固定在内存中为好alter table table_name storage(buffer_pool keep)。

4、并行

    如果在表一级设置了较高的并行度，如alter table table_name parallel(degree 10)，通常会使CBO错误的选择全表扫描。通常不建议在表级的设置并行。并行查询通常可以提高全表扫描的性能，建议在语句级用HINTS来实现并行，如/*+full(table_name) parallel(table_name degree)*/。

5、全表扫描hints

    如果想强制优化器使用全表扫描可以用提示FULL

I/O是针对数据块的而不是行

    Oracle的I/O是针对数据块的，因此被访问的数据块所占的百分比将影响CBO是否选择全表扫描。通常一个数据块中存储着多条记录，被请求的记录要么聚集在少数几个块中，要么分散在大量的数据块中。

 

HWM(High Water Mark)

    HWM是全表扫描范围的标记，每个全表扫描都要读到HWM位置。当表analyze之后可以在DBA_TABLES.BLOCKS查到HWM，当表被drop、truncate或者move之后，HWM将会被重置。需要注意的是，当一个表被大量删除记录之后，HWM下面的大量数据块是空的，此时若对此表进行全表扫描，Oracle仍然会读到HWM位置，会对全表扫描的性能产生极坏的影响。

 

Rowid扫描

    Rowid就是一个记录在数据块中的位置，由于指定了记录在数据库中的精确位置，因此rowid是检索单条记录的最快方式。如果通过rowid来访问表，Oracle首先需要获得被检索记录的rowid，Oracle可以在WHERE子句中得到rowid，但更多的是通过索引扫描来获得，然后Oracle基于rowid来定位被检索的每条记录。优化器何时使用Rowid 并不是每个索引扫描都伴随着rowid的访问，如果索引中包含了被访问的所有字段，则不再需要通过rowid来访问表。注意： Rowid是Oracle表示数据存储的内部方法，它可能会由于版本的改变而改变。不推荐通过在WHERE中指定rowid来访问数据，因为行迁移和行链接会导致rowid变化，exp/imp也会使rowid变化。

 

索引扫描

    索引不仅包含被索引字段的值，还包含表中行的位置标识rowid，如果语句只检索索引字段，Oracle直接从索引中读取该值而不去访问表，如果语句通过索引检索其他字段值，则Oracle通过rowid访问表中记录。

    索引扫描类型： 索引唯一扫描（Index Unique Scans） 索引范围扫描（Index Range Scans）索引降序范围扫描（Index Range Scans Descending）索引跳跃扫描（Index Skip Scans）全索引扫描（Full Scans）快速全索引扫描（Fast Full Index Scans） 索引连接（Index Joins）位图连接（Bitmap Joins）

    1、索引唯一扫描

    这种扫描通常发生在对一个主键字段或含有唯一约束的字段指定相等条件时，只有单行记录被访问。

    2、索引范围扫描

    索引范围扫描是检索数据的常用方式，返回的数据返照索引字段升序排列，字段值相同的则按照rowid升序排列。如果在语句中指定了order by字句，而且排序字段是索引字段时Oracle将忽略order by子句。

例如：

SQL> select * from t;

COLX              COLY

--------------- ---------------

1                3

1                2         

1                1

1                0

SQL> create index ind_t on t(coly);

SQL> set autotrace on

SQL> select * from t where coly>0;

COLX             COLY

--------------- ---------------

1                1

1                2

1                3

Execution Plan

 ----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T'

2 1 INDEX (RANGE SCAN) OF 'IND_T' (NON-UNIQUE)

    没有使用order by结果集已经是按coly升序排列的。

SQL> set autotrace traceonly

SQL> select * from t where coly>0 order by coly;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T'

2 1 INDEX (RANGE SCAN) OF 'IND_T' (NON-UNIQUE)

    可以看到执行计划中无SORT 步骤，说明Oracle忽略了order by子句。

    3、索引降序范围扫描

    如果在order by中指定了索引是降序排列的，或者使用了index_desc提示，Oracle可能会使用索引降序范围扫描。例如：

SQL> select /*+index_desc(t ind_t)*/ colx,coly from t where coly<3;

COLX            COLY

--------------- ---------------

1                2

1                1

1                0

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=4 Bytes=104)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=4 Bytes=104)

2 1 INDEX (RANGE SCAN DESCENDING) OF 'IND_T' (NON-UNIQUE) (Cost=2 Card=1)

    4、索引跳跃式扫描

    跳跃式扫描发生在复合索引中，它在逻辑上将索引分离为较小的子索引，当复合索引的某一个字段不在查询中指定时，它将被跳过，从而提高索引扫描的效率。可以使用index_ss提示强制使用跳跃扫描。举个例子：

SQL> select* from employees;

SEX  EMPLOYEE_ID     ADDRESS

---- --------------- --------------------

F    98              ABC

F    100             ABC

F    102             ABC

F    104             ABC

M    101             ABC

M    103             ABC

M    105             ABC

SQL> create index ind_sex_empid on employees(sex,employee_id);

 索引结构如下图所示：

 SQL>set autotrace traceonly

SQL>select /*+index_ss(employees ind_sex_empid)*/ * from employees where employee_id=101;

 Execution Plan

 ----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=11)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' (Cost=3 Card=1 Bytes=11)

2 1 INDEX (SKIP SCAN) OF 'IND_SEX_EMPID' (NON-UNIQUE) (Cost=2 Card=1)

    5、全索引扫描

    如果要使用全索引扫描必须满足两个条件，一是查询涉及的字段都包含在索引中，二是至少一个索引字段具有非空属性。由于索引键的数据是有序的，因此全索引扫描可以用消除排序操作。全索引扫描只需要一次I/O。

select empno,ename from big_emp order by empno,ename;

Execution Plan

-------------------------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=11)

1 0 INDEX (FULL SCAN) OF ‘BE_IX' (Cost=2 Card=1)

    6、快速全索引扫描

   快速全索引扫描只访问索引本身，而不去访问表，因此只有查询涉及的字段都包含在索引中时才会使用快速全索引扫描。如果想使用快速全索引扫描查询所涉及的字段必须全部包含在索引中，而且索引中至少有一个字段具有非空属性。满足条件后可以使用index_ffs提示来强制使用快速全索引扫描，快速全索引扫描只适用于CBO。快速全索引扫描并不能消除排序操作，因为索引键中的数据没有被排序。不同于全索引扫描，快速全索引扫描是通过多块读取的方式来读取整个索引的，并可以设置并行方式。

    7、索引连接

    只有查询涉及的所有字段都包含在索引中，才会使用索引连接，此时只通过访问索引就能获得所有需要的数据，而不用访问表。索引连接只适用于CBO，且不能消除排序操作。可以通过index_join提示来强制使用索引连接。

    8、位图连接

    位图连接使用一个位图作为键，然后通过映射函数将比特位转换为rowid。只有Oracle9i企业版才支持位图索引和位图索引连接。

 

Sample Table Scans

Sample table scan是随机检索表中的数据，当FROM后面有SAMPLE或SAMPLE BLOCK子句时，会执行Sample table scan。如：

SELECT * FROM employees SAMPLE BLOCK (1);

 

CBO如何选择访问路径

    CBO首先检查WHERE子句中的条件以及FROM子句，确定有哪些访问路径是可用的。然后CBO使用这个访问路径产生一组可能的执行计划，再通过索引、表的统计信息评估每个计划的成本，最后优化器选择成本最低的一个。

    例1： SELECT * FROM employees WHERE last_name = 'JACKSON';

如果last_name具有唯一约束或者主键约束，优化器了解到只有一行数据被返回，这种情况下查询具有很强的选择性，优化很可能走唯一索引扫描。

    例2：还是上面的语句，如果last_name不具有唯一约束或主键约束，优化器使用USER_TAB_COLUMNS.NUM_DISTINCT和USER_TABLES.NUM_ROWS的统计信息来评估查询的选择性，估算last_name为jackson的记录占了employees表的比例。

    例3： SELECT * FROM employees WHERE employee_id < 7500;

评估这个查询的选择性时优化器使用WHERE子句中的边界值7500和employee_id字段的USER_TAB_COLUMNS.HIGH_VALUE、USER_TAB_COLUMNS.LOW_VALUE，优化器假定在最小值和最大值之间employee_id是平均分布的，优化器确定值小于7500的百分比，然后把这个值作为这个查询的选择性。

    例4： SELECT * FROM employees WHERE employee_id < :e1;

优化器并不知道e1的值，绑定变量的值每次运行都可能不同，因此优化器不能使用前面的方法来评估含有绑定变量的查询的选择性，在这种情况下优化器会使用内部缺省值试探着估算一个选择性。

    例5：

   SELECT * FROM employees WHERE employee_id BETWEEN :low_e AND :high_e;

优化器会将这句改写为： employee_id >= :low_e employee_id <= :high_e 然后优化器仍然是用内部缺省值来试探着为其评估一个选择性。

    例6： SELECT * FROM employees WHERE employee_id BETWEEN 7500 AND 7800; 优化器会改写为： employee_id >= 7500 employee_id <= 7800 优化器为每个条件独立的评估选择性（S1和S2），然后用下列公式计算BETWEEN的选择性：S=ABS（S1+S2-1)

 

理解连接

    CBO如何运行连接语句为一个连接语句选择一个执行计划，优化器必须做出下列相关决策： 1、访问路径优化器必须给连接语句中的每个表选择一个可用来检索数据的路径。 2、连接方法 Oracle必须为每对行源执行连接操作，连接的方法包括嵌套循环、排序合并、散列连接、笛卡尔积等。 3、连接顺序如果需要连接的表多于两个，Oracle先连接其中两个表然后将其连接的结果与下一个表做连接，直到所有的表都被连接。

CBO如何选择连接方法

    优化器评估每个连接方法的成本，然后选择成本最低的一个。如果一个返回多行的连接，优化器将考虑如下三个因素：当返回大量的结果集（大于1万行），嵌套循环连接是效率很低的，优化器可能不会选择它。嵌套循环的成本主要在把外表中所有被选择的行与内表匹配的过程，CBO的连接顺序可以用ORDERED提示来改变。嵌套循环连接的成本计算公式： cost = access cost of A + (access cost of B * number of rows from A) 如果你使用CBO，当返回大量结果集时使用散列连接效率是非常高的。散列连接的成本计算公式： cost = (access cost of A * number of hash partitions of B) + access cost of B 如果你使用RBO，当返回大量结果集时排序合并连接的效率比较高。排序合并连接的成本主要在于把所有行源读到内存中，进行排序的过程，多块读取对排序合并连接会有所帮助。排序合并连接的成本计算公式： cost = access cost of A + access cost of B + (sort cost of A + sort cost of B) 当数据是预先排序的，后面两个排序成本为0。

CBO如何运行Anti-joins

   SELECT * FROM employees WHERE department_id NOT IN (SELECT department_id FROM departments WHERE location_id = 1700); 优化器缺省是用嵌套循环来处理anti-joins的，但是如果使用了MERGE_AJ、HASH_AJ、NL_AJ提示，NOT IN能够被转换为一个排序合并或hash anti-join。

CBO如何运行Semi-joins

    SELECT * FROM departments WHERE EXISTS (SELECT * FROM employees WHERE departments.department_id = employees.department_id AND employees.salary > 2500); 优化器缺省也是用嵌套循环来执行EXISTS的，也同样可以通过MERGE_SJ、HASH_SJ、NL_SJ提示来调整。通常建议把NOT IN用EXISTS来改写，但是当NOT IN的子查询中包含OR分支时不能转为EXISTS。

   嵌套循环连接对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择。在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大（大于1万不适合），要把返回子集较小表的作为外表（CBO默认外表是驱动表），而且在内表的连接字段上一定要有索引。当然也可以用ORDERED提示来改变CBO默认的驱动表，使用USE_NL(table_name1 table_name2)可是强制CBO执行嵌套循环连接。

    散列连接散列连接是CBO做大数据集连接时的常用方式，优化器使用两个表中较小的表（或数据源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O的性能。也可以用USE_HASH(table_name1 table_name2)提示来强制使用散列连接。如果使用散列连接HASH_AREA_SIZE初始化参数必须足够的大，如果是9i，Oracle建议使用SQL工作区自动管理，设置WORKAREA_SIZE_POLICY为AUTO，然后调整PGA_AGGREGATE_TARGET即可

    排序合并连接通常情况下散列连接的效果都比排序合并连接要好，然而如果行源已经被排过序，在执行排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。可以使用USE_MERGE(table_name1 table_name2)来强制使用排序合并连接。以下情况Oracle可能会选择使用排序合并连接： l 两个表做非等值连接 l OPTIMIZER_MODE被设置成RULE l HASH_JOIN_ENABLE设置成FALSE l 已经事先排过序，优化器认为使用排序合并连接的成本要比散列连接低。 l HASH_AREA_SIZE和SORT_AREA_SIZE设置太小，优化器认为散列连接成本过高。外连接不论是嵌套循环外连接还是散列外连接，CBO不会根据成本去选择连接顺序，被驱动的表总是含有（+）的一方。