上山容易,下山难,数据库索引也是这样,建时容易,删时难
在项目中,索引基本上是这么产生的
1,建主键送索引
2,感觉sql慢,来个索引先
3,查询条件变了,再来个索引
4,查询条件变多了,来个组合索引
5,查询条件要经过计算,来个函数索引
6,性别之类的列来个位图索引
7,见不得TABLE ACCESS FULL,想法设法加个索引
8,不想回表
如果是自己团队开发的应用,大家沟通一下,该删的删,该调整的调整,基本上不会有太大问题,如果是第三方原装未开封的产品,无法修改代码,调整索引的影响谁都不知道,调整后可能没事,也可能关键页面打不开,但是也不能不调整,索引的性能先不说,至少对数据插入的影响是可见的,这种场景下,不可视索引可以作为索引调整的手段。
如果将现有索引设置成不可视,针对该表的查询将不会使用该索引,不可视索引的维护依然是正常的,可以保证随时启用
示例
索引设置成不可视
SQL> alter index idx_test_name invisible;
Index altered.
使用索引列查询该表
SQL> select * from test where object_name='TEST';
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 2484 | 308 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 12 | 2484 | 308 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='TEST')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
200 recursive calls
0 db block gets
1172 consistent gets
0 physical reads
0 redo size
1611 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
将索引启用,再次执行查询语句
SQL> alter index idx_test_name visible;
Index altered.
Elapsed: 00:00:00.04
SQL> select * from test where object_name='TEST';
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 3700989122
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 207 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_NAME | 1 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='TEST')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
204 recursive calls
0 db block gets
92 consistent gets
0 physical reads
0 redo size
1614 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
索引设置成不可视后,如果应用出现问题,可以立即启用索引,因为不可视索引是正常维护的,所以启用非常快,如果按照常规的删除索引,然后重建,在业务高峰期的时候,重建索引的后果大家都懂。