?? 访问索引的最大和最小值，ORACLE做了哪些优化呢？不同的版本是否有限制呢？索引是有序存储的，因此，获取索引的最大值，只需要扫描最右（最左desc索引）叶子块，最小值，只需要扫描最左（最右desc索引）叶子块即可，在RBO中就有这种优化访问路径了。见下
?? 访问索引的最大和最小值，ORACLE做了哪些优化呢？不同的版本是否有限制呢？索引是有序存储的，因此，获取索引的最大值，只需要扫描最右（最左desc索引）叶子块，最小值，只需要扫描最左（最右desc索引）叶子块即可，在RBO中就有这种优化访问路径了。见下图（摘自ORACLE 11G CONCEPTS）：
如下例：
单独访问MAX,MIN，可以走INDEX FULL SCAN MIN/MAX访问路径
|
dingjun123@ORADB> show rel release 1102000100 DROP TABLE t; dingjun123@ORADB> SELECT MAX(object_id) FROM t; Execution Plan Statistics dingjun123@ORADB> SELECT MIN(object_id) FROM t; Execution Plan Note Statistics |
通过这种高效的访问路径，只需要扫描最左或最右边的一个叶子块即可，只找1行数据，上面统计信息显示只需要2个IO就搞定了。如果要一条SQL同时获得MIN/MAX呢？
|
–先收集统计信息 dingjun123@ORADB> EXEC dbms_stats.gather_table_stats(ownname => USER,tabname => ‘t’,estimate_percent => 100,cascade => TRUE); PL/SQL procedure successfully completed. ? dingjun123@ORADB> SELECT MAX(object_id) max_obj,MIN(OBJECT_id) min_obj FROM t; Execution Plan |
? ? 竟然走的全表扫描，很显然，我这里的索引，是比表小很多，应该只访问索引就可以了，虽然object_id无NOT NULL约束，但是max/min(列）运算已经告之ORACLE,这里肯定不包含NULL，但是：
|
dingjun123@ORADB> SELECT MAX(object_id) max_obj,MIN(OBJECT_id) min_obj FROM t?WHERE object_id IS NOT NULL; 1 row selected. Execution Plan |
? ? 显式加WHERE OBJECT_ID IS NOT NULL告诉ORACLE,那么计划改变，走INDEX FAST FULL SCAN，这是正确的。这可以认定是此版本下ORACLE优化器的一个限制。通过实验发现，单个组函数对索引运算，可以走索引，但是2个或以上的就不行了。
|
–单个组函数对索引运算，走索引 dingjun123@ORADB> SELECT SUM(object_id) ?FROM t; Execution Plan ———————————————————- Plan hash value: 2371838348 ——————————————————————————- | Id ?| Operation ? ? ? ? ? ? | Name ?| Rows ?| Bytes | Cost (%CPU)| Time ? ? | ——————————————————————————- | ? 0 | SELECT STATEMENT ? ? ?| ? ? ? | ? ? 1 | ? ? 5 | ? ?48 ? (3)| 00:00:01 | | ? 1 | ?SORT AGGREGATE ? ? ? | ? ? ? | ? ? 1 | ? ? 5 | ? ? ? ? ? ?| ? ? ? ? ?| | ? 2 | ??INDEX FAST FULL SCAN| IDX_T | 75264 | ? 367K| ? ?48 ? (3)| 00:00:01 | ——————————————————————————- –多个组函数对索引运算，不走索引，需要NOT NULL约束或手动加IS NOT NULL条件 –无NOT NULL，使用HINT也是失效的 ? dingjun123@ORADB> SELECT SUM(object_id) ?,count(object_id) FROM t?WHERE object_id IS NOT NULL; –多个组函数实验，省略，当然多个组函数 |
? ? ??那么这种情况下，ORACLE为什么不走INDEX FULL?MIN/MAX呢，很显然SELECT MIN,MAX… FROM 是不行的。可以转换一下思路：既然单个组函数操作，可以走索引，特别是MIN,MAX的操作，可以高效走INDEX FULL MIN,MAX，那么就可以使用2条SQL，然后合并即可:
|
–使用UNION ALL，缺点，不能直接知道谁大谁小，还得进一步运算 –访问2次索引，使用的都是FULL MIN/MAX路径，因此IO增加1倍，4个IO dingjun123@ORADB> SELECT MIN(object_id) FROM t ? 2 ?UNION ALL ? 3 ?SELECT MAX(object_id) FROM t; 2 rows selected. Execution Plan Statistics –使用标量子查询，好处，最大哪个列是最大值，哪个列是最小值 Execution Plan Statistics |
? ? 第一种UNION ALL方式可以行列转换一下：
|
dingjun123@ORADB> SELECT MAX(decode(rn,1,val)) min_obj, MAX(decode(rn,2,val)) max_obj ? 2 ?FROM ( ? 3 ?SELECT MIN(object_id) val,1 rn FROM t ? 4 ?UNION ALL ? 5 ?SELECT MAX(object_id),2 rn FROM t ? 6 ?); ? ?MIN_OBJ ? ?MAX_OBJ |
? ?下面举一个实例说明INDEX FULL SCAN MIN/MAX的使用：
需求：查询出最小OBJECT_ID的所有信息。用多种方法实现：
|
–先将object_id变为NOT NULL约束 DELETE FROM t WHERE object_id IS NULL; ALTER TABLE t MODIFY object_id NOT NULL; –1.分析函数，因为OBJECT_ID有索引，11G的分页函数也可以谓词推进，如果走索引可以WINDOW NOSORT STOPKEY，效率不错，5个IO Execution Plan –2.使用传统分页ROWNUM,走STOPKEY,3个IO，效率最好 Execution Plan Statistics –3.子查询实现，子查询走高效的FULL INDEX SCAN?MIN MAX,外部查询条件又是OBJECT_ID,走INDEX RANGE SCAN，6个IO，效率比上面的差，但是不算太差 Execution Plan ? ?2 – access(“T”.”OBJECT_ID”= (SELECT MIN(“OBJECT_ID”) FROM “T” “T”)) Statistics |
? ? 当然，如果要同时找最大最小值索引的全部信息：
|
–不能图方便，不是简单的SQL就是好的SQL，无法走STOP KEY和降序索引扫描，逻辑读1474 dingjun123@ORADB> SELECT * ? 2 ?FROM ( ? 3 ?SELECT t.*,row_number() over(ORDER BY object_id) rn1, ? 4 ?row_number() over(ORDER BY object_id DESC) rn2 ? 5 ?FROM t ? 6 ?) WHERE rn1=1 OR rn2=1; 2 rows selected. Execution Plan Predicate Information (identified by operation id): Statistics –使用UNION ALL，两条语句都很高效，后面的可以走DESC扫描并STOP KEY，逻辑读8 Execution Plan –传统ROWNUM分页UNION ALL，效率最好，逻辑读6 Execution Plan Predicate Information (identified by operation id): –使用MIN,MAX扫描，并且子查询走索引，逻辑读11，虽然不及前2个，但是还不错，并且SQL简单 Execution Plan |