首页 » ORACLE » why not use index?

why not use index?

今天看到个贴子问为什么不走索引?
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少一倍

打赏

对不起,这篇文章暂时关闭评论。