谁能解释一下create index i on t(c desc)的用途

〇〇

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_5010.htm
看了官方文档，只看懂就是desc当作函数索引，其他没看出来

dingjun123

默认是asc升序索引，desc就是降序索引
降序索引好像只能在CBO情况下使用，如果按索引键降序排序，能快速排序，如果按索引键升序排序，那么可能走 DESCENDING

create table test as select * from dba_users;

create index idx_username_desc on test(username desc);

  begin
   dbms_stats.gather_table_stats('DINGJUN123','TEST');
end;

explain plan for select * from test where username>'SYS' order by username;
select * from table(dbms_xplan.display);

Plan hash value: 3259162827

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |     5 |   495 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | TEST              |     5 |   495 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN DESCENDING| IDX_USERNAME_DESC |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(SYS_OP_DESCEND("USERNAME")<HEXTORAW('ACA6ACFF') )
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("USERNAME"))>'SYS' AND
              SYS_OP_DESCEND("USERNAME")<HEXTORAW('ACA6ACFF') )
              
              
explain plan for select * from test where username>'SYS' order by username desc;
select * from table(dbms_xplan.display);

1        Plan hash value: 921883186
2         
3        -------------------------------------------------------------------------------------------------
4        | Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
5        -------------------------------------------------------------------------------------------------
6        |   0 | SELECT STATEMENT            |                   |     5 |   495 |     2   (0)| 00:00:01 |
7        |   1 |  TABLE ACCESS BY INDEX ROWID| TEST              |     5 |   495 |     2   (0)| 00:00:01 |
8        |*  2 |   INDEX RANGE SCAN          | IDX_USERNAME_DESC |     1 |       |     1   (0)| 00:00:01 |
9        -------------------------------------------------------------------------------------------------
10         
11        Predicate Information (identified by operation id):
12        ---------------------------------------------------
13         
14           2 - access(SYS_OP_DESCEND("USERNAME")<HEXTORAW('ACA6ACFF') )
15               filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("USERNAME"))>'SYS' AND
16                      SYS_OP_DESCEND("USERNAME")<HEXTORAW('ACA6ACFF') )

〇〇

看不出2执行计划的差别

dingjun123

原帖由 〇〇 于 2009-11-13 12:28 发表
看不出2执行计划的差别

INDEX RANGE SCAN DESCENDING
INDEX RANGE SCAN
难道不差别吗？我这里数据量小，其他的应该没有什么差别，如果数据量大，cost,cpu之类的有可能有差别，没有测试

chengboxian

单独列索引用DESC感觉没有什么特殊的用途，
但联合索引使用了DESC在topN 查询中会起很大的作用
例如 create index i on t(b,c desc,d)

EnochPersist

2 |   INDEX RANGE SCAN DESCENDING| IDX_USERNAME_DESC
2 |   INDEX RANGE SCAN          | IDX_USERNAME_DESC
这两个地方的区别

EnochPersist

我测试了1百万数据的表 两个没有什么区别

dingjun123

原帖由 chengboxian 于 2009-11-13 12:49 发表
单独列索引用DESC感觉没有什么特殊的用途，
但联合索引使用了DESC在topN 查询中会起很大的作用
例如 create index i on t(b,c desc,d)

回去查查资料学习下

tom0732

降序索引,大数据量排序时有用

newkid

Oracle 9i10g编程艺术 BY THOMAS KYTE
11.2.3 降序索引
降序索引（descending index）是Oracle8i 开始引入的，用以扩展B*树索引的功能。它允许在索引
中以降序（从大到小的顺序）存储一列，而不是升序（从小到大）存储。在之前的Oracle 版本（即Oracle8i
以前的版本）中，尽管语法上也支持DESC（降序）关键字，但是一般都会将其忽略，这个关键字对于索引
中数据如何存储或使用没有任何影响。不过，在Oracle8i 及以上版本中，DESC 关键字确实会改变创建和
使用索引的方式。
Oracle 能往前读索引，这种能力已不算新，所以你可能会奇怪我们为什么会兴师动众地说这个特性
很重要。例如，如果使用先前的表T，并如下查询这个表：
Oracle 会往前读索引。这个计划最后没有排序步骤：数据已经是有序的。不过，如果你有一组列，
其中一些列按升序排序（ASC），另外一些列按降序排序（DESC），此时这种降序索引就能派上用场了，例如：
ops$tkyte@ORA10G> set autotrace traceonly explain
ops$tkyte@ORA10G> select owner, object_type
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 order by owner DESC, object_type DESC;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=82 Card=11395 Bytes=170925)

Oracle 会往前读索引。这个计划最后没有排序步骤：数据已经是有序的。不过，如果你有一组列，
其中一些列按升序排序（ASC），另外一些列按降序排序（DESC），此时这种降序索引就能派上用场了，例如：
ops$tkyte@ORA10G> select owner, object_type
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 order by owner DESC, object_type ASC;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=85 Card=11395 Bytes=170925)
11.0 SORT (ORDER BY) (Cost=85 Card=11395 Bytes=170925)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=82 Card=11395 ...
Oracle 不能再使用（OWNER, OBJECT_TYPE, OBJECT_NAME）上的索引对数据排序。它可以往前读得到
按OWNER DESC 排序的数据，但是现在还需要“向后读“来得到按OBJET_TYPE 顺序排序（ASC）数据。此时
Oracle 的实际做法是，它会把所有行收集起来，然后排序。但是如果使用DESC 索引，则有：

ops$tkyte@ORA10G> create index desc_t_idx on t(owner desc,object_type asc);
Index created.
ops$tkyte@ORA10G> exec dbms_stats.gather_index_stats( user, 'DESC_T_IDX' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA10G> select owner, object_type
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 order by owner DESC, object_type ASC;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=11395 Bytes=170925)
11.0 INDEX (RANGE SCAN) OF 'DESC_T_IDX' (INDEX) (Cost=2 Card=11395 ...
现在，我们又可以读取有序的数据了，在这个计划的最后并没有额外的排序步骤。应当注意，除非
init.ora 中的compatible 参数设置为8.11.0 或更高，否则CREATE INDEX 上的DESC 选项会被悄悄地忽略，
没有警告，也不会产生错误，因为这是先前版本的默认行为。
注意查询中最好别少了ORDER BY。即使你的查询计划中包含一个索引，但这并不表示数据会以“某
种顺序“返回。要想从数据库以某种有序的顺序获取数据，惟一的办法就是在查询中包括一个ORDER
BY 子句。ORDER BY 是无可替代的。