为什么desc类型的索引不能被用与计算 min/max?

来源：互联网发布：英克软件下载编辑：程序博客网时间：2024/06/05 23:54

不管是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)

0 0