今天看到个贴子问为什么不走索引？
create index xx on xx(colxx DESC)
select rowid from xx order by colxx
我模拟一下
note:
建立索引时指定了DESC keyword，其实是一个基于函数的索引，ASC keyword不受影响，但又区别于函数索引，倒序索引不会在段上增加隐藏列，但是函数索引会在表段上增加一个列如SYS_NC00004$。
如果where条件中未指定列上的值条件是（排除is not null）就无法用到函数，即使加了hint都不会使用索引，但加了=值操作时走索引时注意调用了一个oracle文档中没有提到的内部函数sys_op_descend，
sys_op_descend - Returns value as it would be used in a descending index. This is essentially
reverse() function with output in hex.
当创建了一个普通索引时开始也是不走索引，因为这是一个全部数据库的检索CBO不确定列上有没有NULL值，所以在WHERE条件后加了is not null或加上not null约束就改为index,注意有个地方因hint，而选择了IFS，无hint是IFFS,估算IFFS cost比IFS少一倍
create index xx on xx(colxx DESC)
select rowid from xx order by colxx
我模拟一下
anbob@ANBOB>conn system/oracle
Connected.
system@ANBOB>create table anbob.obj as select object_id,object_name,object_type from dba_objects where rownum<1000;
Table created.
system@ANBOB>conn anbob/anbob;
Connected.
anbob@ANBOB>create index idx_objty_desc on obj(object_type desc );
Index created.
anbob@ANBOB>execute dbms_stats.gather_table_stats(user,'OBJ',cascade=>true);
PL/SQL procedure successfully completed.
anbob@ANBOB>set autot trace exp
anbob@ANBOB>select rowid from obj order by object_type desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 2960038505
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 18981 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 999 | 18981 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| OBJ | 999 | 18981 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
anbob@ANBOB>select /*+index (obj) */rowid from obj order by object_type desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 2960038505
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 18981 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 999 | 18981 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| OBJ | 999 | 18981 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
anbob@ANBOB>select /*+index (obj) */rowid from obj where object_type is not null order by object_type desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 2960038505
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 18981 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 999 | 18981 | 4 (25)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| OBJ | 999 | 18981 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_TYPE" IS NOT NULL)
anbob@ANBOB>select /*+ index (obj) */object_type from obj where object_type is not null ;
Execution Plan
----------------------------------------------------------
Plan hash value: 730912574
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 6993 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| OBJ | 999 | 6993 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE" IS NOT NULL)
anbob@ANBOB>select /*+ index (obj) */object_type from obj order by object_type desc ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2960038505
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 6993 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 999 | 6993 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| OBJ | 999 | 6993 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
anbob@ANBOB>col index_name for a30
anbob@ANBOB>select index_name,index_type,blevel,num_rows,avg_leaf_blocks_per_key,distinct_keys from user_indexes;
INDEX_NAME INDEX_TYPE BLEVEL NUM_ROWS AVG_LEAF_BLOCKS_PER_KEY DISTINCT_KEYS
------------------------------ ------------------------------------------------------ ---------- ---------- ----------------------- -------------
IDX_OBJTY_DESC FUNCTION-BASED NORMAL 1 999 1 10
anbob@ANBOB>select object_type from obj d where object_type='TABLE' ;
Execution Plan
----------------------------------------------------------
Plan hash value: 4079391523
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 700 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_OBJTY_DESC | 100 | 700 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(SYS_OP_DESCEND("OBJECT_TYPE")=HEXTORAW('ABBEBDB3BAFF') )
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("OBJECT_TYPE"))='TABLE')
下面建立个普通索引
anbob@ANBOB>create index idx_obj on obj(object_type);
Index created.
anbob@ANBOB>analyze index idx_obj compute statistics;
Index analyzed.
anbob@ANBOB>select index_name,index_type,blevel,num_rows,avg_leaf_blocks_per_key,distinct_keys from user_indexes
INDEX_NAME INDEX_TYPE BLEVEL NUM_ROWS AVG_LEAF_BLOCKS_PER_KEY DISTINCT_KEYS
------------------------------ ------------------------- ---------- ---------- ----------------------- -------------
IDX_OBJTY_DESC FUNCTION-BASED NORMAL 1 999 1 10
IDX_OBJ NORMAL 1 999 1 10
anbob@ANBOB>set autot trace exp
anbob@ANBOB>select rowid from obj order by object_type;
Execution Plan
----------------------------------------------------------
Plan hash value: 2960038505
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 18981 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 999 | 18981 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| OBJ | 999 | 18981 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
anbob@ANBOB>select /* +index (obj)*/rowid from obj order by object_type;
Execution Plan
----------------------------------------------------------
Plan hash value: 2960038505
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 18981 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 999 | 18981 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| OBJ | 999 | 18981 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
anbob@ANBOB>select /*+index (obj idx_obj) */object_type from obj order by object_type ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2960038505
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 6993 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 999 | 6993 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| OBJ | 999 | 6993 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
anbob@ANBOB>select /*+index (obj idx_obj) */object_type from obj where object_type is not null order by object_type ;
Execution Plan
----------------------------------------------------------
Plan hash value: 675211661
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 6993 | 4 (0)| 00:00:01 |
|* 1 | INDEX FULL SCAN | IDX_OBJ | 999 | 6993 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE" IS NOT NULL)
anbob@ANBOB>select /*+index (obj idx_obj) */rowid from obj where object_type is not null ;
Execution Plan
----------------------------------------------------------
Plan hash value: 675211661
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 18981 | 4 (0)| 00:00:01 |
|* 1 | INDEX FULL SCAN | IDX_OBJ | 999 | 18981 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------
anbob@ANBOB>select rowid from obj where object_type is not null ;
Execution Plan
----------------------------------------------------------
Plan hash value: 195390768
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 18981 | 2 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_OBJ | 999 | 18981 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
anbob@ANBOB>alter table obj modify object_type not null;
Table altered.
anbob@ANBOB>select rowid from obj ;
Execution Plan
----------------------------------------------------------
Plan hash value: 195390768
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 11988 | 2 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| IDX_OBJ | 999 | 11988 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
anbob@ANBOB>select rowid from obj order by object_type;
Execution Plan
----------------------------------------------------------
Plan hash value: 1390887327
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 18981 | 3 (34)| 00:00:01 |
| 1 | SORT ORDER BY | | 999 | 18981 | 3 (34)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| IDX_OBJ | 999 | 18981 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
延伸
sys@ANBOB>select object_id from dba_objects where object_name='OBJ' and owner='ANBOB';
OBJECT_ID
----------
60721
sys@ANBOB>select col#,segcol#,name from col$ where obj#=60721;
COL# SEGCOL# NAME
---------- ---------- ------------------------------------------------------------
1 1 OBJECT_ID
2 2 OBJECT_NAME
3 3 OBJECT_TYPE
sys@ANBOB>create index anbob.idx_up on anbob.obj(lower(object_name));
Index created.
sys@ANBOB>select col#,segcol#,name from col$ where obj#=60721;
COL# SEGCOL# NAME
---------- ---------- ------------------------------------------------------------
1 1 OBJECT_ID
2 2 OBJECT_NAME
3 3 OBJECT_TYPE
0 0 SYS_NC00004$
note:
建立索引时指定了DESC keyword，其实是一个基于函数的索引，ASC keyword不受影响，但又区别于函数索引，倒序索引不会在段上增加隐藏列，但是函数索引会在表段上增加一个列如SYS_NC00004$。
如果where条件中未指定列上的值条件是（排除is not null）就无法用到函数，即使加了hint都不会使用索引，但加了=值操作时走索引时注意调用了一个oracle文档中没有提到的内部函数sys_op_descend，
sys_op_descend - Returns value as it would be used in a descending index. This is essentially
reverse() function with output in hex.
当创建了一个普通索引时开始也是不走索引，因为这是一个全部数据库的检索CBO不确定列上有没有NULL值，所以在WHERE条件后加了is not null或加上not null约束就改为index,注意有个地方因hint，而选择了IFS，无hint是IFFS,估算IFFS cost比IFS少一倍
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
