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的重复值很多，这样写效率就不高。
