单表查询某列最大最小值的性能问题(二)
ration | 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
-----------------------------------------
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操作时引入的索引数量不是这个参数控制。
最大最小值的查询操作，就