ANBOB™

“索引可以禁用么？禁用如何启用？禁用了索引查询时会出错么？”

下面看我的实验

anbob@ANBOB>create table testidx
  2  as select rownum id,'anbob'||rownum name from dual connect by rownum<1000
  3  
anbob@ANBOB>create table testidx pctfree 0
  2  as select rownum id,'anbob'||rownum name from dual connect by rownum<1000;

Table created.

anbob@ANBOB>create index idx_testidx on testidx(id);

Index created.

anbob@ANBOB>create index idx_fun_testidx on testidx(upper(name));

Index created.



anbob@ANBOB>alter index idx_testidx disable;
alter index idx_testidx disable
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option


anbob@ANBOB>alter index idx_fun_testidx disable;

Index altered.


anbob@ANBOB>select index_name,index_type,funcidx_status,STATUS from user_indexes where table_name='TESTIDX';

INDEX_NAME                     INDEX_TYPE                  FUNCIDX_ STATUS
------------------------------ --------------------------- -------- --------
IDX_FUN_TESTIDX                FUNCTION-BASED NORMAL       DISABLED VALID
IDX_TESTIDX                    NORMAL                               VALID

anbob@ANBOB>select * from testidx where upper(name)='ANBOB1';
select * from testidx where upper(name)='ANBOB1'
*
ERROR at line 1:
ORA-30554: function-based index ANBOB.IDX_FUN_TESTIDX is disabled

anbob@ANBOB>set autot trace exp

anbob@ANBOB>select /*+ FULL(T) */ * from testidx T where upper(name)='ANBOB1';

Execution Plan
----------------------------------------------------------
Plan hash value: 3565063929

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |    10 |   610 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TESTIDX |    10 |   610 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------


anbob@ANBOB>select /*+ NO_INDEX(T idx_fun_testidx) */ * from testidx T where upper(name)='ANBOB1';

Execution Plan
----------------------------------------------------------
Plan hash value: 3565063929

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |    10 |   610 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TESTIDX |    10 |   610 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

anbob@ANBOB>alter index idx_fun_testidx unusable;

Index altered.
anbob@ANBOB>set autot trace exp
anbob@ANBOB>select  * from testidx T where upper(name)='ANBOB1';

Execution Plan
----------------------------------------------------------
Plan hash value: 3565063929

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |    10 |   610 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TESTIDX |    10 |   610 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

anbob@ANBOB>show parameter skip
anbob@ANBOB>set autot off
anbob@ANBOB>show parameter skip

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes                boolean     TRUE
anbob@ANBOB>alter session set skip_unusable_indexes=FALSE;

Session altered.

anbob@ANBOB>set autot trace exp
anbob@ANBOB>select  * from testidx T where upper(name)='ANBOB1';
select  * from testidx T where upper(name)='ANBOB1'
*
ERROR at line 1:
ORA-30554: function-based index ANBOB.IDX_FUN_TESTIDX is disabled

anbob@ANBOB>alter index idx_testidx unusable;

Index altered.

anbob@ANBOB>select  * from testidx T where ID=1;
select  * from testidx T where ID=1
*
ERROR at line 1:
ORA-01502: index 'ANBOB.IDX_TESTIDX' or partition of such index is in unusable state


anbob@ANBOB>alter session set skip_unusable_indexes=TRUE;

Session altered.

anbob@ANBOB>select  * from testidx T where ID=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3565063929

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

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

   1 - filter("ID"=1)
   
SQL> l
  1* select name,value,isdefault from v$parameter where upper(name)='SKIP_UNUSABLE_INDEXES'
SQL> /

NAME                           VALUE                ISDEFAULT
------------------------------ -------------------- ---------
skip_unusable_indexes          TRUE                 TRUE

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

anbob@ANBOB>select index_name,index_type,funcidx_status,STATUS from user_indexes where table_name='TESTIDX';

INDEX_NAME                     INDEX_TYPE                  FUNCIDX_ STATUS
------------------------------ --------------------------- -------- --------
IDX_FUN_TESTIDX                FUNCTION-BASED NORMAL       DISABLED UNUSABLE
IDX_TESTIDX                    NORMAL                               UNUSABLE

anbob@ANBOB>alter index IDX_TESTIDX rebuild;
Index altered.

anbob@ANBOB>alter index IDX_fun_TESTIDX rebuild online;
Index altered.

anbob@ANBOB>select index_name,index_type,funcidx_status,STATUS from user_indexes where table_name='TESTIDX';

INDEX_NAME                     INDEX_TYPE                  FUNCIDX_ STATUS
------------------------------ --------------------------- -------- --------
IDX_FUN_TESTIDX                FUNCTION-BASED NORMAL       ENABLED  VALID
IDX_TESTIDX                    NORMAL                               VALID


anbob@ANBOB>alter index IDX_fun_TESTIDX  disable;
Index altered.

anbob@ANBOB>alter index IDX_fun_TESTIDX  enable;

Index altered.

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.

note:
1,disable 只能用于函数索引，可以通过rebuild 或enable来重新启用
2,disable 函数索引后如果查询用到，就会报错，可以指定 full,no_index 来批定全表或不用该索引跳过错误，也可以把该索引unusable掉
3,unusable 的索引在查询时受参数skip_unusable_indexes 影响，true表示跳过不可用索引，false反之，db version 9i及更老的版本没有该参数会报错。
4,skip_unusable_indexes 参数在imp/impdp时也存在，unsable状态的index在后期不在自动维护index key，重新起用要rebuild;

打赏

微信扫一扫，打赏作者吧～