单表查询某列最大最小值的性能问题

在oracle 10g中，有一个单表查询的SQL语句,它没有where子句，只是简单地同时求某列最大值和最小值。
按照理解，它应该走全索引扫描，但它却走了全表扫描。单表的数据量有点大，组成也有点复杂，LOB字段很多，索引有点多，加lob的索引一起有13个。这下性能就差很多，本来预计毫秒级别的操作变成了分钟。在其他同版本的库上，索引较少时，会走全索引扫描，但性能也不好，查询时的一致性读也很大。

SQL是这样：select  max(updateid),min(updateid) from dbcenter.TABLE_NAME ;
很简单，而且updateid列上有一个唯一索引。索引也分析过，但现在执行起来却性能差的很，致命的全表扫描。


首先，使用set autotrace trace exp stat得到真实的执行计划。
SQL> set timing on
SQL> set autotrace trace exp stat
SQL> set linesize 300

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |     7 |   373K  (1)| 01:14:42 |
|   1 |  SORT AGGREGATE    |                |     1 |     7 |            |          |
|   2 |   TABLE ACCESS FULL| TABLE_NAME |  8665K|    57M|   373K  (1)| 01:14:42 |
-------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    1700621  consistent gets
    1506260  physical reads
          0  redo size
        602  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

从结果中可以看到走的就是全表扫描。从统计值看，也是真正的全表扫描了，从头扫到尾巴的那种，没办法，表中这个字段的值又不是排序的，不全部扫完不知道最大最小值的。

很显然，这不是最优的结果。我认为最理想应该是走updateid列的索引，一个索引快速全扫描就行。

猜测，会不会是索引多了不知道如何选择。在select子句中是不主动选择索引的？

但是，我使用hint也没有效果，优化器依然没有选择走这个索引。


select/*+index_ffs(TABLE_NAME IDX55021287)*/ MAX(updateid), MIN(updateid) from dbcenter.TABLE_NAME;

Elapsed: 00:03:28.77

Execution Plan
----------------------------------------------------------


-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |     7 |   373K  (1)| 01:14:42 |
|   1 |  SORT AGGREGATE    |                |     1 |     7 |            |          |
|   2 |   TABLE ACCESS FULL| TABLE_NAME |  8665K|    57M|   373K  (1)| 01:14:42 |
-------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    1701902  consistent gets
    1497285  physical reads
          0  redo size
        602  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

但是，如果只查max或min时，会走索引。

select  MIN(updateid) from dbcenter.TABLE_NAME ;

Execution Plan
----------------------------------------------------------
Plan hash value: 3935799349

------------------------------------------------------------------------------------------
| Id  | Operation                  | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |     1 |     7 |   373K  (1)| 01:14:42 |
|   1 |  SORT AGGREGATE            |             |     1 |     7 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| IDX55021287 |  8665K|    57M|            |          |
------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        524  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

性能也好的很，一致性读只有3。这样的结果也很好理解。索引是唯一索引，已经排序好的，求一个最大值，肯定只要扫描索引的开始或者结束部分的数据块即可。
          
                    
因此，需要分析一下这个SQL的执行计划产生的过程。我使用event 10053 trace name context forever ,level 1方法来完成这个操作。

alter system flush shared_pool;
alter session set "_optimizer_search_limit"=15;
oradebug setmypid;
oradebug event 10053 trace name context forever ,level 1;
explain plan for select  max(updateid),min(updateid) from dbcenter.TABLE_NAME ;

***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Table: TABLE_NAME  Alias: TABLE_NAME     
    Card: Original: 8663996  Rounded: 8663996  Computed: 8663996.00  Non Adjusted: 8663996.00
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  373495.00  Resp: 373495.00  Degree: 0
      Cost_io: 372211.00  Cost_cpu: 18442053762
      Resp_io: 372211.00  Resp_cpu: 18442053762
******** Begin index join costing ********
  ****** trying bitmap/domain indexes ******
  Access Path: index (FullScan)
    Index: IDX242025
    resc_io: 25019.00  resc_cpu: 1911171307
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 2515.21  Resp: 2515.21  Degree: 0
  Access Path: index (FullScan)
    Index: IDX94341804
    resc_io: 31023.00  resc_cpu: 1953914433
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 3115.90  Resp: 3115.90  Degree: 0
  Access Path: index (FullScan)
    Index: PK_TABLE_NAME
    resc_io: 25217.00  resc_cpu: 1912567352
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 2535.02  Resp: 2535.02  Degree: 0
  Access Path: index (FullScan)
    Index: IDX242025
    resc_io: 25019.00  resc_cpu: 1911171307
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 2515.21  Resp: 2515.21  Degree: 0
  ****** finished trying bitmap/domain indexes ******
******** End index join costing ********
  Best:: AccessPath: TableScan
         Cost: 373495.00  Degree: 1  Resp: 373495.00  Card: 8663996.00  Bytes: 0
***************************************

从结果看，优化器在index join costing操作时，并没有将IDX55021287索引计算进来。

即使我使用了alter session set "_optimizer_search_limit"=15;将限制值从5提升到15也没有效果。或许，index join costing操作时引入的索引数量不是这个参数控制。

最大最小值的查询操作，就不应该在SQL中一步完成，应该分步骤实现。很显然，oracle的查询重写没有那么智能，没有将其分开。即使在11g也不行，我测试过了。