不管是desc还是asc, 索引中已经排好了顺序;计算min/max直接可以访问相关索引结构,无需全表扫描; 如下所式: desc索引无法支持min/max的查询;而asc类型的索引确实可以.为何这样?是跟索引的物理设计和实现有关吗?
SQL> create table t as select *from dba_objects;
表已创建。
SQL> create index t_idx on t(object_id desc)
2 ;
索引已创建。
SQL> analyze table t compute statistics;
表已分析。
SQL> select max(object_id) from t;
MAX(OBJECT_ID)
--------------
31801
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=43 Card=30188 Bytes=120
752)
SQL> select min(object_id) from t;
MIN(OBJECT_ID)
--------------
2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=43 Card=30188 Bytes=120
752)
SQL> select min(object_id) from t;
MIN(OBJECT_ID)
--------------
2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN (MIN/MAX)) OF 'T_IDX2' (NON-UNIQUE) (Co
st=43 Card=30188 Bytes=120752)
SQL> create table t as select *from dba_objects;
表已创建。
SQL> create index t_idx on t(object_id desc)
2 ;
索引已创建。
SQL> analyze table t compute statistics;
表已分析。
SQL> select max(object_id) from t;
MAX(OBJECT_ID)
--------------
31801
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=43 Card=30188 Bytes=120
752)
SQL> select min(object_id) from t;
MIN(OBJECT_ID)
--------------
2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=43 Card=30188 Bytes=120
752)
SQL> select min(object_id) from t;
MIN(OBJECT_ID)
--------------
2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN (MIN/MAX)) OF 'T_IDX2' (NON-UNIQUE) (Co
st=43 Card=30188 Bytes=120752)