|
alter index index_name disable,enable针对函数索引。
SQL> create table test as select * from all_objects; SQL> create index ind_t_object_id on test(object_id) nologging;
SQL> exec dbms_stats.gather_table_stats(user,'test',cascade => true);
SQL> set autotrace traceonly
SQL> select * from test where object_id = 20;
执行计划
----------------------------------------------------------
Plan hash value: 255872589
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 96 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 96 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=20)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
910 bytes sent via SQL*Net to client
327 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 ind_t_object_id disable;
alter index ind_t_object_id disable
*
第 1 行出现错误:
ORA-02243: ALTER INDEX 或 ALTER MATERIALIZED VIEW 选项无效
SQL> alter index ind_t_object_id unusable;
索引已更改。
SQL> select * from test where object_id = 20;
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 96 | 168 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 96 | 168 (1)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=20)
统计信息
----------------------------------------------------------
237 recursive calls
0 db block gets
795 consistent gets
0 physical reads
0 redo size
910 bytes sent via SQL*Net to client
327 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> drop index ind_t_object_id;
索引已删除。
SQL> create index ind_t_object_id on test(to_char(object_id)) nologging;
索引已创建。
SQL> select * from test where to_char(object_id) = '20';
执行计划
----------------------------------------------------------
Plan hash value: 255872589
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 518 | 49728 | 24 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 518 | 49728 | 24 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OBJECT_ID | 207 | | 1 (0)| 00:00:01 |
--------------------------------- |