FRIST_ROWS下，CBO的一个默认行为

以前只知道这样一个说法，first_rows下的CBO，更倾向于使用索引，最快的得到第一行数据
那这个倾向是如何倾向的了？今天就遇到了一个简单的例子

 SQL> select /*+ index(T_FILE_CMS_FILE_S IDX_C_PARENT_ID) */ * from tt.T_FILE_CMS_FILE_S where C_PARENT_ID<86000 order by C_ID;
 
 已选择7行。
 
 
 执行计划
 ----------------------------------------------------------
 Plan hash value: 730101589
 
 --------------------------------------------------------------------------------------------------
 | Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
 --------------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT             |                   |   292 |   102K|   161   (1)| 00:00:02 |
 |   1 |  SORT ORDER BY               |                   |   292 |   102K|   161   (1)| 00:00:02 |
 |   2 |   TABLE ACCESS BY INDEX ROWID| T_FILE_CMS_FILE_S |   292 |   102K|   160   (0)| 00:00:02 |
 |*  3 |    INDEX RANGE SCAN          | IDX_C_PARENT_ID   |   292 |       |     2   (0)| 00:00:01 |
 --------------------------------------------------------------------------------------------------
 
 Predicate Information (identified by operation id):
 ---------------------------------------------------
 
    3 - access("C_PARENT_ID"<86000)
 
 
 统计信息
 ----------------------------------------------------------
           1  recursive calls
           0  db block gets
           8  consistent gets
           3  physical reads
           0  redo size
        4469  bytes sent via SQL*Net to client
         385  bytes received via SQL*Net from client
           2  SQL*Net roundtrips to/from client
           1  sorts (memory)
           0  sorts (disk)
           7  rows processed
 
 SQL> select * from tt.T_FILE_CMS_FILE_S where C_PARENT_ID<86000 order by C_ID;
 
 已选择7行。
 
 
 执行计划
 ----------------------------------------------------------
 Plan hash value: 4146844463
 
 -------------------------------------------------------------------------------------------------
 | Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT            |                   |   292 |   102K| 22716   (1)| 00:04:33 |
 |*  1 |  TABLE ACCESS BY INDEX ROWID| T_FILE_CMS_FILE_S |   292 |   102K| 22716   (1)| 00:04:33 |
 |   2 |   INDEX FULL SCAN           | SYS_C008931       | 90516 |       |   204   (1)| 00:00:03 |
 -------------------------------------------------------------------------------------------------
 
 Predicate Information (identified by operation id):
 ---------------------------------------------------
 
    1 - filter("C_PARENT_ID"<86000)
 
 
 统计信息
 ----------------------------------------------------------
           1  recursive calls
           0  db block gets
       22699  consistent gets
        3542  physical reads
           0  redo size
        4469  bytes sent via SQL*Net to client
         385  bytes received via SQL*Net from client
           2  SQL*Net roundtrips to/from client
           0  sorts (memory)
           0  sorts (disk)
           7  rows processed

CBO选择了成本高的那个执行计划，进行了索引全扫描
分析了下10053
 SINGLE TABLE ACCESS PATH
   Column (#6): C_PARENT_ID(NUMBER)
     AvgLen: 6.00 NDV: 4486 Nulls: 0 Density: 0.003367 Min: 85560 Max: 11786925
     Histogram: HtBal  #Bkts: 127  UncompBkts: 127  EndPtVals: 51
   Table: T_FILE_CMS_FILE_S  Alias: T_FILE_CMS_FILE_S    
     Card: Original: 90779  Rounded: 306  Computed: 305.65  Non Adjusted: 305.65
   Access Path: TableScan
     Cost:  830.23  Resp: 830.23  Degree: 0
       Cost_io: 826.00  Cost_cpu: 60141430
       Resp_io: 826.00  Resp_cpu: 60141430
   Access Path: index (RangeScan)
     Index: IDX_C_PARENT_ID
     resc_io: 167.00  resc_cpu: 1547300
     ix_sel: 0.003367  ix_sel_with_filters: 0.003367
     Cost: 167.11  Resp: 167.11  Degree: 1
   Best:: AccessPath: IndexRange  Index: IDX_C_PARENT_ID
          Cost: 167.11  Degree: 1  Resp: 167.11  Card: 305.65  Bytes: 0
 ***************************************
 OPTIMIZER STATISTICS AND COMPUTATIONS
 ***************************************
 GENERAL PLANS
 ***************************************
 Considering cardinality-based initial join order.
 ***********************
 Join order[1]:  T_FILE_CMS_FILE_S[T_FILE_CMS_FILE_S]#0
 ORDER BY sort
     SORT resource      Sort statistics
       Sort width:          28 Area size:      131072 Max Area size:     5242880
       Degree:               1
       Blocks to Sort:      16 Row size:          406 Total Rows:            306
       Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
       Total IO sort cost: 0      Total CPU sort cost: 14332741
       Total Temp space used: 0
 ***********************
 Best so far: Table#: 0  cost: 168.1168  card: 305.6532  bytes: 110160

CBO开始也确认了通过IDX_C_PARENT_ID索引，在排序的成本是最低的
但是，对于order by并且order by列上有索引的时候，其又计算了一个
 ****** Recost for ORDER BY (using index) ************
 ***************************************
 SINGLE TABLE ACCESS PATH
   Table: T_FILE_CMS_FILE_S  Alias: T_FILE_CMS_FILE_S    
     Card: Original: 90779  Rounded: 306  Computed: 305.65  Non Adjusted: 305.65
   Access Path: TableScan
     Cost:  830.23  Resp: 830.23  Degree: 0
       Cost_io: 826.00  Cost_cpu: 60141430
       Resp_io: 826.00  Resp_cpu: 60141430
   Access Path: index (FullScan)
     Index: SYS_C008931
     resc_io: 22697.00  resc_cpu: 272235644
     ix_sel: 1  ix_sel_with_filters: 1
     Cost: 22716.15  Resp: 22716.15  Degree: 1
   Best:: AccessPath: IndexRange  Index: SYS_C008931
          Cost: 22716.15  Degree: 1  Resp: 22716.15  Card: 305.65  Bytes: 360
 ***********************
 Join order[1]:  T_FILE_CMS_FILE_S[T_FILE_CMS_FILE_S]#0
 ***********************
 Best so far: Table#: 0  cost: 22716.1460  card: 305.6532  bytes: 110160
 (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
 *********************************
 Number of join permutations tried: 1
 *********************************
     SORT resource      Sort statistics
       Sort width:          28 Area size:      131072 Max Area size:     5242880
       Degree:               1
       Blocks to Sort:      16 Row size:          406 Total Rows:            306
       Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
       Total IO sort cost: 0      Total CPU sort cost: 14332741
       Total Temp space used: 0
 Final - First Rows Plan:  Best join order: 1
   Cost: 22716.1460  Degree: 1  Card: 306.0000  Bytes: 110160
   Resc: 22716.1460  Resc_io: 22697.0000  Resc_cpu: 272235644
   Resp: 22716.1460  Resp_io: 22697.0000  Resc_cpu: 272235644
 kkoipt: Query block SEL$1 (#0)

最后，其根据计算全表扫描后再排序还是通过索引避免排序的计算后，得到通过索引避免排序最差
其最后选择了通过索引避免排序而完全不理会前面SINGLE TABLE ACCESS PATH的结果，并且也不管index FullScan的成本比TableScan的成本还高
因为其认为那样能更快返回第一行数据

对比了first_rows_1000和all_rows，都能正确计算和返回结果

对于FRIST_ROWS为何要这样做，在METALINK上找到了答案
Fast response optimization (FIRST_ROWS variants) [ID 154354.1]

1. FIRST_ROWS
-------------

The optimizer uses a mix of costs and heuristics to find the best plan for
fast delivery of the first few rows. The heuristic sometimes leads the CBO
to generate a plan whose cost is significantly larger than the cost of a plan
without applying the heuristic. FIRST_ROWS is available for backward
compatibility and plan stability.

Some of the heuristics are:

a. The full table scans are considered to have an infinite cost when comparing
it with other plans. Therefore, plans that contain full table scans will only
be used if there are no alternative plans that use indexes instead. Be aware
that the optimizer does not consider all indexes on the table, but only the
one that is induced by the query (see Note 67522.1)

b. Any ORDER BY clause can induce the index access

When using an index access in the query can avoid a final sort, then this
plan will be used.

This heuristics can be fine tuned by setting the sort_elimination_cost_ratio
init.ora. This cost ratio dictates how expensive a plan with ORDER BY sort
elimination can be before it is rejected in favor of a cheaper plan which uses
a sort. For example, a value of 5 means that a plan that avoids a sort may not be
more than 5 times more expensive than a plan that does not avoid it. The default
is 0 (no value) which means a plan with ORDER BY sort elimination will be chosen
even if it is infinitely more expensive.

c. The OR expansions are disabled. It is the same as if the hint NO_EXPAND is
specified.

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

转载于:http://blog.itpub.net/8242091/viewspace-620212/