alter index index_name disable,enable针对函数索引。
alter index index_name unusable针对普通索引http://www.cndba.cn/hbhe0316/article/108573http://www.cndba.cn/hbhe0316/article/108573http://www.cndba.cn/hbhe0316/article/108573

USABLE | UNUSABLE

Specify UNUSABLE to mark the index or index partition(s) or index subpartition(s) UNUSABLE. The space allocated for an index or index partition or subpartition is freed immediately when the object is marked UNUSABLE. An unusable index must be rebuilt, or dropped and re-created, before it can be used. While one partition is marked UNUSABLE, the other partitions of the index are still valid. You can execute statements that require the index if the statements do not access the unusable partition. You can also split or rename the unusable partition before rebuilding it. Refer to CREATE INDEX … USABLE | UNUSABLE for more information.

ONLINEhttp://www.cndba.cn/hbhe0316/article/108573

Specify ONLINE to indicate that DML operations on the table or partition will be allowed while marking the index UNUSABLE. If you specify this clause, then the database will not drop the index segments.http://www.cndba.cn/hbhe0316/article/108573

Restrictions on Marking Indexes Unusable

The following restrictions apply to marking indexes unusable:http://www.cndba.cn/hbhe0316/article/108573http://www.cndba.cn/hbhe0316/article/108573

You cannot specify UNUSABLE for an index on a temporary table.

DISABLE Clause
DISABLE applies only to a function-based index. This clause lets you disable the use of a function-based index. You might want to do so, for example, 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.http://www.cndba.cn/hbhe0316/article/108573http://www.cndba.cn/hbhe0316/article/108573

SQL> create table t1 as select * from all_objects;

Table created.

SQL> create index index_t1 on t1(object_id) nologging;

Index created.

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

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly

SQL> select * from t1 where object_id = 88;


Execution Plan
----------------------------------------------------------
Plan hash value: 1963305094

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

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

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

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

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1       |     1 |   132 |     2
 (0)| 00:00:01 |

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

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=88)


Statistics
----------------------------------------------------------
         66  recursive calls
          0  db block gets
        103  consistent gets
          0  physical reads
          0  redo size
       2691  bytes sent via SQL*Net to client
        399  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> alter index index_t1 unusable;

Index altered.


SQL> select * from t1 where object_id = 88;


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   132 |   388   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |   132 |   388   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=88)


Statistics
----------------------------------------------------------
         38  recursive calls
          0  db block gets
       1452  consistent gets
          0  physical reads
          0  redo size
       2691  bytes sent via SQL*Net to client
        399  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed


删除索引
SQL> drop index index_t1;

Index dropped.


创建函数索引
SQL> create index index_t1 on t1(to_char(object_id)) nologging;

Index created.


SQL> select * from t1 where to_char(object_id) = '88';


Execution Plan
----------------------------------------------------------
Plan hash value: 1963305094

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

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

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

|   0 | SELECT STATEMENT                    |          |   732 | 96624 |    64
 (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1       |   732 | 96624 |    64
 (0)| 00:00:01 |

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

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(TO_CHAR("OBJECT_ID")='88')


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
          6  consistent gets
          1  physical reads
          0  redo size
       2691  bytes sent via SQL*Net to client
        410  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> alter index index_t1 disable;

Index altered.

SQL> select * from t1 where to_char(object_id) = '88';
select * from t1 where to_char(object_id) = '88'
*
ERROR at line 1:
ORA-30554: function-based index SYS.INDEX_T1 is disabled

版权声明：本文为博主原创文章，未经博主允许不得转载。

oracle