Alter index index_name disable,enable for the function index.
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;
Execution plan
----------------------------------------------------------
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)
Statistical information
----------------------------------------------------------
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
*
An error occurred on line 1th:
Ora-02243:alter INDEX or ALTER materialized VIEW option is invalid
sql> alter index ind_t_object_id unusable;
The index has changed.
Sql> SELECT * FROM Test where object_id = 20;
Execution plan
----------------------------------------------------------
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)
Statistical information
----------------------------------------------------------
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;
The index has been deleted.
Sql> CREATE index ind_t_object_id on test (To_char (object_id)) nologging;
The index has been created.
Sql> SELECT * FROM Test where to_char (object_id) = ' 20 ';
Execution plan
----------------------------------------------------------
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 |
-----------------------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
2-access (To_char ("object_id") = ' 20 ')
Statistical information
----------------------------------------------------------
Recursive calls
0 db Block gets
5 Consistent gets
1 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;
The index has changed.
Sql> SELECT * FROM Test where to_char (object_id) = ' 20 ';
SELECT * FROM Test where to_char (object_id) = ' 20 '
*
An error occurred on line 1th:
ORA-30554: Function-based index FWMS4GZ_DEV_DDL. IND_T_OBJECT_ID is disabled
Official documents:
DISABLE Clause:
DISABLE applies only to a function-based index. This clause lets your disable the use of a function-based index. You might want to do so, for example and while working on the body of the function. Afterward you can either rebuild the index or specify another ALTER index statement with the ENABLE keyword.
Unusable Clause:
Unusable Clause specify unusable to mark the index or index partition (s) or index subpartition (s) unusable. An unusable index must is rebuilt, or dropped and re-created, before it can be used. While one partition was marked unusable, the other partitions of the index was still valid. You can execute statements that require the index if the statements does not access the unusable partition. You can also split or rename the unusable partition before rebuilding it.
The difference between Oracle alter index disable/unusable
