[20160816]索引可以disable吗.txt
--如果有人将索引可以disable,我是不相信的.我仅仅知道索引可以隐藏,可以设置UNUSABLE.
--但是能disable吗?看一些文档,做一些测试:
1.环境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SCOTT@test01p> create table deptx as select * from dept;
Table created.
create index pk_deptx on deptx(deptno);
create index if_deptx_dname on deptx(upper(dname));
SCOTT@test01p> select index_name,index_type,funcidx_status,STATUS from user_indexes where table_name='DEPTX';
INDEX_NAME INDEX_TYPE FUNCIDX_ STATUS
--------------- --------------------------- -------- --------
IF_DEPTX_DNAME FUNCTION-BASED NORMAL ENABLED VALID
PK_DEPTX NORMAL VALID
--//注意看视图中funcidx_status=ENABLED,对应的索引名IF_DEPTX_DNAME,并且是函数索引.
--//说明函数索引可以disable.
2.测试是否可以disable.
SCOTT@test01p> alter index pk_deptx disable;
alter index pk_deptx disable
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
SCOTT@test01p> alter index IF_DEPTX_DNAME disable;
Index altered.
--可以发现函数索引确实可以disable.
3.执行sql命令看看:
SCOTT@test01p> select * from deptx where upper(dname)='SALES';
select * from deptx where upper(dname)='SALES'
*
ERROR at line 1:
ORA-30554: function-based index SCOTT.IF_DEPTX_DNAME is disabled
SCOTT@test01p> host oerr ora 30554
30554, 00000, "function-based index %s.%s is disabled"
// *Cause: An attempt was made to access a function-based index that has
// been marked disabled because the function on which the index
// depends has been changed.
// *Action: Perform one of the following actions:
// -- drop the specified index using the DROP INDEX command
// -- rebuild the specified index using the ALTER INDEX REBUILD command
// -- enable the specified index using the ALTER INDEX ENABLE command
// -- make the specified index usable using the ALTER INDEX UNUSABLE
// command
SCOTT@test01p> select /*+ full(deptx) */* from deptx where upper(dname)='SALES';
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO
3.索引enable:
SCOTT@test01p> alter index IF_DEPTX_DNAME enable;
Index altered.
SCOTT@test01p> select index_name,index_type,funcidx_status,STATUS from user_indexes where table_name='DEPTX';
INDEX_NAME INDEX_TYPE FUNCIDX_ STATUS
------------------------------ --------------------------- -------- --------
IF_DEPTX_DNAME FUNCTION-BASED NORMAL ENABLED VALID
PK_DEPTX NORMAL VALID
SCOTT@test01p> select * from deptx where upper(dname)='SALES';
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3paxd0n67t28f, child number 0
-------------------------------------
select * from deptx where upper(dname)='SALES'
Plan hash value: 4284093818
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPTX | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IF_DEPTX_DNAME | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DEPTX@SEL$1
2 - SEL$1 / DEPTX@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTX"."SYS_NC00004$"='SALES')
总结:
--可以发现仅仅函数索引可以disable.