min ? max ? 執行計劃？

create table big_table as select * from dba_objects;
insert into big_table select * from big_table;
insert into big_table select * from big_table;

ALTER TABLE BIG_TABLE MODIFY(OBJECT_ID NULL);

CREATE INDEX I_BT_OBJECT_ID ON BIG_TABLE(OBJECT_ID)

select min(object_id),max(object_id) from big_table;

第1次看到類似的sql語句的時候，感覺會使用索引，並且會走
index full scan (min/max).但是仔細看執行計劃發現,發現是使用全表掃描。修改約束OBJECT_ID NOT NULL，僅僅計劃變為INDEX FAST FULL SCAN。

修改為
SELECT MIN (object_id), MAX (object_id)
FROM big_table
WHERE object_id IS NOT NULL;

執行計劃走INDEX FAST FULL SCAN。

但是單獨寫SELECT MIN (object_id) FROM big_table;
執行計劃就是INDEX FULL SCAN (MIN/MAX)。

如何要執行類似的sql應該將sql語句修改如下：
SELECT a.m1, b.m2
FROM (SELECT MAX (object_id) m1
FROM big_table) a,
(SELECT MIN (object_id) m2
FROM big_table) b

對比如下：
Execution Plan
----------------------------------------------------------
Plan hash value: 2118989048

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 26 | 6 (0)| 00:00:01 |
| 2 | VIEW | | 1 | 13 | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 5 | | |
| 4 | INDEX FULL SCAN (MIN/MAX)| I_BT_OBJECT_ID | 405K| 1978K| 3 (0)| 00:00:01 |
| 5 | VIEW | | 1 | 13 | 3 (0)| 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | 5 | | |
| 7 | INDEX FULL SCAN (MIN/MAX)| I_BT_OBJECT_ID | 405K| 1978K| 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size

SELECT MIN (object_id), MAX (object_id)
FROM big_table
WHERE object_id IS NOT NULL;

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 307 (4)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | INDEX FAST FULL SCAN| I_BT_OBJECT_ID | 405K| 1978K| 307 (4)| 00:00:04 |
----------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1386 consistent gets
0 physical reads
0 redo size

再回到生產系統，一般要執行類似的sql，比如：

select min(a),max(a) from t where xx=:1 ,如果索引建立再xx,a上，如果xx的重複值很多，這樣寫效率就不高。