ITPub博客

首页 > 数据库 > Oracle > oracle invisible index与unusable index的区别

oracle invisible index与unusable index的区别

原创 Oracle 作者:pxbibm 时间:2020-04-23 16:01:26 0 删除 编辑

不可见索引(Invisible Index)是ORACLE 11g引入的新特性。不可见索引是会被优化器忽略的不可见索引,除非在会话或系统级别上将OPTIMIZER_USE_INVISIBLE_INDEXES初始化参数显式设置为TRUE。此参数的默认值是FALSE。

   (1)从11g开始,就有了invisible index

   (2)invisible index会被优化器所忽略,但是dml操作仍然会维护索引,批量提交数据不会提升性能

   (3)如果在session或者system级别使用参数OPTIMIZER_USE_INVISIBLE_INDEXES=true,那么优化器会考虑使用invisible index

   (4)你可以使一个分区索引变成invisible index,但是你不能使单个分区变成invisible index,而其他分区visible

   (5)invisible index只是让优化器不可见,索引段中的数据还是存在的,并且dml操作维护索引,所以visible index后,不需要重建索引

   (6)创建 invisible index    

            CREATE INDEX 索引名 ON 表名(列名)  INVISIBLE;

   (7) 现有索引变成invisible index

            ALTER INDEX 索引名 INVISIBLE;

虚拟索引是为了合理、科学新增索引而设计的,而不可见索引是为了合理、科学的删除索引而设计的。

为什么这样说呢? 因为DBA在维护索引时,我们经常会找出无用或低效的索引,并删除这些索引,在生产环境下,删除索引还是有一定风险的,即使ORACLE提供了监控索引使用情况的技术。例如,某些索引在监控周期中未被使用到,就会认为索引是无用的而被删除。当结束监控后,后续又使用到了索引,可能就会对系统性能造成冲击。这时,可能就会手忙脚乱的去找回索引定义语句、重建索引。11G之前,我们可以先不删除索引,而将其修改为unusable。这样的话,索引的定义并未删除,只是索引不能再被使用也不会随着表数据的更新而更新。当需要重新使用该索引时,需要用rebuild语句重建、然后更新统计信息。对于一些大表来说,这个时间可能就非常长。在ORACLE 11g里提供了一个新的特性来降低直接删除索引或者禁用索引的风险,那就是索引不可见(Index Invisible)。我们可以将无用或低效的索引设置为不可见索引,当观察一段时间后,发现其对系统性能并无任何影响,那么就可以彻底删除索引了。

当你在批量加载数据的时候,想要改变性能或者测试删除索引后的性能,可以使用unusable index 或者是 invisible index

unusable index


(1)unusable index 是被优化器所忽略,并且不被dml操作维护。但是unusable index 可以改变批量加载的性能

(2)任何现有的索引变成unusable后,索引段都会被删除

(3)由于unusable index 是不会被dml操作维护,索引变成unusable index后必须通过rebuild index 或者drop index  and create index

(4)当参数  SKIP_UNUSABLE_INDEXES =true时

dml操作时针对表的操作,对于索引是不会维护的。

唯一约束使用的索引变成unusable,dml操作会报错。

对于非分区索引,对于select 语句,优化器不考虑任何unusable index   ,除非明确指明使用 index hint强调      

对于分区索引,一个或者多个分区可以unusable,, the optimizer does not consider the index if it cannot determine at query compilation time if any of the index partitions can be pruned  ,除非明确指明使用 index hint强调  .

(5)当参数SKIP_UNUSABLE_INDEXES =FALSE

  当 unusable index 或者分区unusable index ,在dml操作更新索引时,会遇到错误

  当 select 语句遇到  unusable index 或者分区 unusable index 时,优化器不会考虑使用它,但是如果优化器使用了unusable index 就会报错

 (6)创建分区unusable index   

        CREATE INDEX 索引名 ON 表名 (列名)

        LOCAL (PARTITION 分区名 UNUSABLE, PARTITION 分区名);

(7) 现有索引变成unusable索引             

        ALTER INDEX 索引名 UNUSABLE;

实例:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> drop table test purge;


Table dropped.


SQL> create table test as select * from dba_objects;


Table created.


SQL> create index indtest on test(object_id);            


Index created.


SQL> exec dbms_stats.gather_table_stats(user,'test',cascade => true);


PL/SQL procedure successfully completed.


SQL> set autotrace traceonly

SQL> set linesize 1000

SQL> select * from test where object_id = 1;    


no rows selected



Execution Plan

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

Plan hash value: 1352592598


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

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

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

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

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |     1 |    98 |     2 (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN     | INDTEST |     1 |       |     1 (0)| 00:00:01 |

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


Predicate Information (identified by operation id):

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


   2 - access("OBJECT_ID"=1)



Statistics

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

  0  recursive calls

  0  db block gets

  2  consistent gets

  0  physical reads

  0  redo size

       1343  bytes sent via SQL*Net to client

509  bytes received via SQL*Net from client

  1  SQL*Net roundtrips to/from client

  0  sorts (memory)

  0  sorts (disk)

  0  rows processed


SQL> alter index indtest invisible;


Index altered.


SQL> select * from test where object_id = 1;


no rows selected



Execution Plan

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

Plan hash value: 1357081020


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

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

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

|   0 | SELECT STATEMENT  | |     1 |    98 |   216   (1)| 00:00:03 |

|*  1 |  TABLE ACCESS FULL| TEST |     1 |    98 |   216   (1)| 00:00:03 |

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


Predicate Information (identified by operation id):

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


   1 - filter("OBJECT_ID"=1)



Statistics

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

22  recursive calls

  0  db block gets

       1252  consistent gets

       1234  physical reads

  0  redo size

       1343  bytes sent via SQL*Net to client

509  bytes received via SQL*Net from client

  1  SQL*Net roundtrips to/from client

  4  sorts (memory)

  0  sorts (disk)

  0  rows processed


SQL> select /*+ index(test indtest)*/ * from test where object_id = 1;


no rows selected



Execution Plan

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

Plan hash value: 1357081020


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

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

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

|   0 | SELECT STATEMENT  | |     1 |    98 |   216   (1)| 00:00:03 |

|*  1 |  TABLE ACCESS FULL| TEST |     1 |    98 |   216   (1)| 00:00:03 |

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


Predicate Information (identified by operation id):

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


   1 - filter("OBJECT_ID"=1)



Statistics

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

  1  recursive calls

  0  db block gets

       1236  consistent gets

       1234  physical reads

  0  redo size

       1343  bytes sent via SQL*Net to client

509  bytes received via SQL*Net from client

  1  SQL*Net roundtrips to/from client

  0  sorts (memory)

  0  sorts (disk)

  0  rows processed


SQL> alter session set optimizer_use_invisible_indexes = true;


Session altered.


SQL> select * from test where object_id = 1;


no rows selected



Execution Plan

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

Plan hash value: 1352592598


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

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

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

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

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |     1 |    98 |     2 (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN     | INDTEST |     1 |       |     1 (0)| 00:00:01 |

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


Predicate Information (identified by operation id):

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


   2 - access("OBJECT_ID"=1)



Statistics

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

  1  recursive calls

  0  db block gets

  2  consistent gets

  0  physical reads

  0  redo size

       1343  bytes sent via SQL*Net to client

509  bytes received via SQL*Net from client

  1  SQL*Net roundtrips to/from client

  0  sorts (memory)

  0  sorts (disk)

  0  rows processed


SQL> 


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

下一篇: 没有了~
全部评论
  • 博文量
    244
  • 访问量
    2203982