ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 关于索引的几个问题---续集2

关于索引的几个问题---续集2

原创 Oracle 作者：lff1530983327 时间：2014-12-31 14:27:36 0 删除 编辑
 

4.为什么明明表中建立了索引，但是在执行计划中却显示未使用我的索引，这是为什么呢？

------不适用建立的索引：情况一

drop table t;

create table t as select decode(mod(rownum,2),0,'M','F')gender, all_objects.* from all_objects;

create index t_idx on t(gender,object_id);

begin

  dbms_stats.gather_table_stats(user,'T');

end;

---------------------------------------------------------------------------------------------------当索引最前列不是只有几个少数值时，可能会选择全表扫描

---------------------------------------------------------------------------------------------------跳跃式索引，查找gender值开始改变的得房，然后开始向下扫描

select * from t where object_id=42;----SELECT STATEMENT            , GOAL = ALL_ROWS   4 1 99

                                    -----TABLE ACCESS BY INDEX ROWID SCOTT T             4 1 99

                                    ----INDEX SKIP SCAN SCOTT T_IDX 3 1 

 

update t set gender =chr(mod(rownum,256));

begin

    dbms_stats.gather_table_stats(user,'T',cascade => TRUE);

  

end;

select * from t where object_id=42;

SQL> select  * from t t1 where object_id=42;

执行计划

----------------------------------------------------------

Plan hash value: 2053318169

--------------------------------------------------------------------------------

-----

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time

    |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |       |     1 |    99 |     4   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    99 |     4   (0)| 00:00:01 |

|*  2 |   INDEX SKIP SCAN           | T_IDX |     1 |       |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------------

-----

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID"=42)

       filter("OBJECT_ID"=42)

统计信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          8  consistent gets

          0  physical reads

          0  redo size

       1681  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

----------------------------------------------------------------------------当索引最前列不是只有几个少数值时，可能会选择全表扫描

SQL> set pagesize 500

SQL> set linesize 500

 

SQL> explain plan for select * from t where object_id=42;

已解释。

SQL> select * from table(dbms_xplan.display);

Plan hash value: 2053318169

--------------------------------------------------------------------------------

-----

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time   |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |       |     1 |    99 |   258   (0)| 00:00:04 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    99 |   258   (0)| 00:00:04 |

|*  2 |   INDEX SKIP SCAN           | T_IDX |     1 |       |   257   (0)| 00:00:04 |

--------------------------------------------------------------------------------

-----

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID"=42)

       filter("OBJECT_ID"=42)

已选择15行。

执行计划

----------------------------------------------------------

Plan hash value: 2137789089

---------------------------------------------------------------------------------------------

| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    29   (0)| 00:00:01 |

|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |  8168 | 16336 |    29   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

统计信息

----------------------------------------------------------

         26  recursive calls

         12  db block gets

        180  consistent gets

          0  physical reads

          0  redo size

       1449  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

         15  rows processed

 ---------------------------------------------------------------------------查找索引列值时会选择索引快速扫描       

SQL> select gender,object_id from t where object_id=42;

G  OBJECT_ID

- ----------

_         42

执行计划

----------------------------------------------------------

Plan hash value: 3163761342

------------------------------------------------------------------------------

| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |       |     1 |     7 |   118   (1)| 00:00:02 |

|*  1 |  INDEX FAST FULL SCAN| T_IDX |     1 |     7 |   118   (1)| 00:00:02 |

------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("OBJECT_ID"=42)

统计信息

----------------------------------------------------------

          1  recursive calls

          2  db block gets

        461  consistent gets

          0  physical reads

          0  redo size

        598  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL>

         select * from t where y=2;即是当索引列含有两个或两个以上字段时，但是查询的时候谓词使用的只是索引列中一个或不足个字段值时，此时查询为了避免调用索引带来的扫描所有索引列的更大开销，

         选择对表做一个全表扫描，而不是索引扫描，但是当使用的谓词只有很少几个不同的值的时候，会选择跳跃式索引。

         另一种：select x,y from t where y=2;此时对索引本身进行一个快速的全面扫描，以为这个缩影比底层的表要小得多。

        

------不适用建立的索引：情况二

-----------------------不适用建立的索引：情况三

索引列上如果要在后面操作中进行函数操作，那么此时我们的操作过程是不会走索引的；

SQL> select * from t1 where x=1;

执行计划

----------------------------------------------------------

Plan hash value: 1369807930

---------------------------------------------------------------------------

| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT |        |     5 |   130 |     1   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| IDX_T1 |     5 |   130 |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("X"=1)

Note

-----

   - dynamic sampling used for this statement (level=2)

SQL> select * from t1 where to_number(x)>1;

执行计划

----------------------------------------------------------

Plan hash value: 3617692013

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    26 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T1   |     1 |    26 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(TO_NUMBER(TO_CHAR("X"))>1)

Note

-----

   - dynamic sampling used for this statement (level=2)

SQL>

-----------------------不适用建立的索引：情况四

create table  t2(x,y,primary key(x))as select rownum x,object_name from all_objects where rownum<20000;

SQL> select * from t2;

执行计划

----------------------------------------------------------

Plan hash value: 1513984157

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      | 18845 |   552K|    27   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T2   | 18845 |   552K|    27   (0)| 00:00:01 |

--------------------------------------------------------------------------

Note

-----

   - dynamic sampling used for this statement (level=2)

SQL> select * from t2 where x<50;

执行计划

----------------------------------------------------------

Plan hash value: 3744695385

--------------------------------------------------------------------------------

------------

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |              |    49 |  1470 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T2           |    49 |  1470 |     3   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | SYS_C0011742 |    49 |       |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("X"<50)

Note

-----

   - dynamic sampling used for this statement (level=2)

SQL> select * from t2 where x=50;

执行计划

----------------------------------------------------------

Plan hash value: 1738664179

--------------------------------------------------------------------------------------------

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |              |     1 |    30 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T2           |     1 |    30 |     2   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | SYS_C0011742 |     1 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("X"=50)

SQL> select * from t2 where x<15000;

执行计划

----------------------------------------------------------

Plan hash value: 1513984157

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      | 14069 |   412K|    27   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T2   | 14069 |   412K|    27   (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("X"<15000)

Note

-----

   - dynamic sampling used for this statement (level=2)

SQL>

可以看出，强制使用索引的效率并没有全表扫描效率高！

 综上所述：建有索引但是没被使用的情况有很多种，，具体情况需要具体分许！！

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

0
0
上一篇： 关于索引的几个问题--续集
下一篇： 数据库维护常用操作命令1--约束
请登录后发表评论 登录
全部评论
lff1530983327

注册时间：2014-11-12

  • 博文量
    72
  • 访问量
    49744

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593