reverse索引，BITMAP 索引，函数索引都只有CBO下才会用

不光是reverse索引，BITMAP 索引，函数索引都只有CBO才会认!

http://www.itpub.net/thread-563349-1-1.html

降序索引好像也是只有CBO下才会有效啊。

以下是降序索引的测试过程：

tstuser@mydb> create index index_t_owner_object_type on t(owner desc ,object_ty
pe desc,object_name) tablespace indx compute statistics;

Index created.

tstuser@mydb> 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
  6  /

3449 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=4 Card=36 Bytes=396)
   1    0   INDEX (RANGE SCAN) OF 'INDEX_T_OWNER_OBJECT_TYPE' (NON-UNIQUE) (Cost
=4 Card=36
           Bytes=396)

 

 

Statistics
----------------------------------------------------------
         56  recursive calls
          0  db block gets
        263  consistent gets
         23  physical reads
          0  redo size
      46594  bytes sent via SQL*Net to client
       2891  bytes received via SQL*Net from client
        231  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       3449  rows processed

tstuser@mydb> analyze table t delete statistics;

Table analyzed.

tstuser@mydb> 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
  6  /

3449 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   SORT (ORDER BY)
   2    1     TABLE ACCESS (FULL) OF 'T'

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        789  consistent gets
          0  physical reads
          0  redo size
      46594  bytes sent via SQL*Net to client
       2891  bytes received via SQL*Net from client
        231  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       3449  rows processed

tstuser@mydb>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8102208/viewspace-623349/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8102208/viewspace-623349/