user_indexes.index_type

scott@ORCL> create index idx_rev on emp(sal) reverse;

Index created.

scott@ORCL> select index_name,index_type from user_indexes where index_name='IDX_REV';

INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
IDX_REV                        NORMAL/REV

       ㈢ 它有什么缺点？
          
          ① if you use reverse key index，index range scan will not work
          ② 当应用需要获取一段范围的数据时，reverse key index将不会被使用，因为键值不是连续的排列的。在这种情况下，CBO将会选择全表扫描
          
          测试：

hr@ORCL> drop table t purge;

Table dropped.

hr@ORCL> create table t (a number,b varchar2(20));

Table created.

hr@ORCL> ed   
Wrote file afiedt.buf

  1  begin
  2    for i in 1..20000
  3    loop
  4      insert into t values(i,to_char(sysdate,'yyyymmddhhmmss'));
  5      commit;
  6    end loop;
  7* end;
hr@ORCL> /

PL/SQL procedure successfully completed.

hr@ORCL> create index idx_t on t (a) reverse;

Index created.

hr@ORCL> set autot on exp

hr@ORCL> select * from t where a >=19989 and a <=19990;

         A B
---------- --------------------
     19989 20130224060219
     19990 20130224060219


Execution Plan
----------------------
Plan hash value