首页 » ORACLE 9i-23ai » Why not use index oracle? 为什么不用索引？ 检查列表
Why not use index oracle? 为什么不用索引？ 检查列表
今天看到个贴子问为什么不走索引？
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少一倍
An Checklist:
1, Is index is valied?
2, Is index is visible?
3, Are you using NOT?
4, Are you using LIKE (‘%% ‘) ?
5, Are you using IS (NOT) NULL ?
6, Are you using the leading columns in a concatenated index?
7, Are the statistics relevant and valid?
8, Does the index exists as all ?
9, Is the query expected to return large portion of the table?
10, It is possible that the data on the table is skewed?
11, Does your query actually trying to use the index?
12, High degree of parallelism of index?
13, is table small?
14, check “_ignore_desc_in_index” and “_optimizer_ignore_hints” when use index hint?
15, Does it use Other indexes?
16, Are you implicitly casting types?
17, Does it use function on column?
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。