4、FIRST_ROWS

CBO尽可能快速的返回结果集的前面少数行记录。

不论是否存在统计信息，都使用CBO优化器，FIRST_ROWS导致CBO使用“试探法”来产生执行计划，这种方式其成本可能会稍大一些。

5、FIRST_ROWS_n

不论是否存在统计信息，都使用CBO优化器，并以最快的速度返回前n行记录，n可以是1,10,100,1000。

2、影响优化器模式及目标的HINTS：

* RULE：意义同OPTIMIZER_MODE=RULE区别在于HINTS作用在语句级，10g中该HINTS已被废弃。

* CHOOSE：    意义同OPTIMIZER_MODE=CHOOSE，10g中已被废弃。

* FIRST_ROWS：意义同OPTIMIZER_MODE=FIRST_ROWS，10g中已被废弃。

* ALL_ROWS：  意义同OPTIMIZER_MODE=ALL_ROWS。

* FIRST_ROWS(n)：意义同OPTIMIZER_MODE=FIRST_ROWS_n。

* CPU_COSTING：启用CPU成本计算，也就是在总成本中考虑CPU的成本，缺省是启用的。该HINTS是10g中新增加的。

* NO_CPU_COSTING：关闭CPU成本计算，也就是在总成本中不考虑CPU的成本，只计算I/O的成本。该HINTS也是10g中新增加的。

五、访问路径

访问路径就是从数据库里检索数据的方式。优化器首先检查WHERE子句和FROM子句的条件，确定有哪些访问路径是可用的。然后优化器使用这些访问路径或各访问路径的联合，产生一组可能存在的执行计划，再通过索引、字段、表的统计信息评估每个计划的成本，最后优化器选择成本最低的执行计划所对应的访问路径。

如果SQL语句的FROM子句无SAMPLE或SAMPLE BLOCK，优化器在选择访问路径的时候会优先考虑语句中的HINTS。

 优化器可用的访问路径如下：

* 全表扫描（Full Table Scans）

* Rowid扫描（Rowid Scans）

* 索引扫描（Index Scans）

* 簇扫描（Cluster Scans）

* 散列扫描（Hash Scans）

* 表取样扫描（Sample Table Scans）

1、全表扫描

全表扫描将读取HWM之下的所有数据块，所有行都要经WHERE子句过滤看是否满足条件。当Oracle执行全表扫描时会按顺序读取每个块且只读一次，如果能够一次读取多个块，可以有效的提高效率，初始化参数DB_FILE_MULTIBLOCK_READ_COUNT用来设置在一次I/O中可以读取多少个数据块。 通常我们认为应该避免全表扫描，但是在检索大量数据时全表扫描优于索引扫描，这正是因为全表扫描可以在一次I/O中读却多个块，从而减少了I/O的次数。在使用全表扫描的同时也可以使用并行来提高扫描的速度。

CBO优化器何时会选择全表扫描

1） 无合适的索引。

2） 检索表中绝大多数的数据。

3） 表非常小。

比如，表中的块小于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）太旧的统计数据。

如果表没有进行过分析或很久没有再次分析，CBO可能会错误的认为表含有及少的数据块。

6）在语句中嵌入了全表扫描的HINTS。

2、Rowid扫描

Rowid表示行在数据块中的具体位置，Rowid是查找具体行的最快方式。可以在WHERE子句中写入Rowid，但是不推荐这么做。通常都是通过索引来获得Rowid，但如果被检索的行都包含在索引中时，直接访问索引就能得到所需的数据则不会使用Rowid。

3、索引扫描

索引不仅包含被索引的字段值，还包含行的位置标识Rowid，如果语句只检索索引字段，Oracle将直接从索引中读取而不需要通过Rowid去访问表，如果语句通过索引检索其他字段值，则Oracle通过索引获得Rowid从而迅速找到具体的行。

索引扫描类型

1） 索引唯一扫描（Index Unique Scans）

2） 索引范围扫描（Index Range Scans）

3） 索引降序范围扫描（Index Range Scans Descending） INDEX_DESC

4） 跳跃式索引扫描（Index Skip Scans）   INDEX_SS

5） 全索引扫描（Full Index Scans）        INDEX_FS

6） 快速全索引扫描（Fast Full Index Scans）   INDEX_FFS

7） 索引连接（Index Joins）   INDEX_JOIN

在解释上述索引扫描类型之前，首先要明确一个问题——Oracle对I/O的评估是针对“块”的而不是“行”。优化器在决定是使用全表扫描还是索引扫描时，看的是所涉及块占全表的比例而不是检索的行占表的比例。当然如果一个块中只包含一个行数据，那么访问块和行是等同的，但是通常情况下都是一个块中含有多个行的数据，因此如果检索的行都聚集在少数块中则会大大降低I/O。

例如：一个有9行数据的表占据三个数据块，在STATUS字段上有一个非唯一索引，该字段共有三类不同的值分别是1、2、3。

第一种情况：

                 Block 1       Block 2        Block 3

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

                 1  1  1       2  2  2        3  3  3  

 索引字段（STATUS）相同的值都聚集在表的同一个物理块中，这种情况下获取STATUS=1的数据只需读取表的一个物理块，即一次I/O。

第二种情况：

                 Block 1       Block 2        Block 3

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

                 1  2  3       1  2  3        1  2  3

索引字段（STATUS）相同的值被分散在表中三个物理块，这时要得到STATUS=1的数据则要读取表的三个物理块，即三次I/O才能获得。

1）唯一索引扫描

 在利用一个主键字段或含有唯一约束的字段选择一行记录时，通常发生唯一索引扫描。

2）索引范围扫描

索引范围扫描返回的数据返照索引字段值升序排列，值相同的按Rowid升序排列。如果在语句中使用了ORDER BY ASC子句，而且排序字段是索引字段时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 ptimizer=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 ptimizer=CHOOSE

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

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

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

优化器在下列情况会使用索引范围扫描：

→ COL1 = :b1

→ COL1 > :b1

→ COL1 < :b1

→ COL1 LIKE ‘ABC%’会做索引范围扫描，而COL1 LIKE ‘%ABC’则不会。

→ 对于复合索引，通常只有复合索引的第一个字段包含在AND条件之中时才会使用复合索引。

3）索引降序范围扫描

如果在order by中指定了索引是降序排列的，或者使用了INDEX_DESC提示，优化器会使用索引降序范围扫描。

例如：

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 ptimizer=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）跳跃式索引扫描

跳跃式索引扫描是用来提高复合索引效率的，通常当复合索引的第一个索引字段不在语句中指定时是无法使用复合索引的，此时如果复合索引的第一个索引字段DISTINCT值非常小，而复合索引的其他索引字段DISTINCT值非常大时，可以使用跳跃式索引扫描来跳过该复合索引的第一个索引字段。跳跃式扫描会使复合索引在逻辑上分裂成N个较小的索引，N值等于复合索引的第一个索引字段的DISTINCT值。

例如：

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 ptimizer=CHOOSE (Cost=3 Card=1 Bytes=11)

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

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12330444/viewspace-324629/ ，如需转载，请注明出处，否则将追究法律责任。