[20171202]关于函数索引的状态.txt
--//我曾经在一篇贴子提到索引可以disable吗?链接:
--//http://blog.itpub.net/267265/viewspace-2123537/
--//实际上仅仅函数索引能disable,为什么呢?实际上自己以前并不搞清楚实际上这个跟oracle使用函数的特殊性有关.
--//如果一个表删除某个字段,对应的索引也会删除.如果定义的函数删除了,对应的函数索引呢?通过例子来说明问题:
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
2.建立测试环境:
SCOTT@test01p> create table t as select rownum id,rownum idx,'test' name from dual connect by level<=5;
Table created.
CREATE FUNCTION p2 (x NUMBER)
RETURN NUMBER DETERMINISTIC IS
BEGIN
RETURN power(x,2);
END;
/
SCOTT@test01p> CREATE INDEX if_t_idx ON t (p2 (idx));
Index created.
SCOTT@test01p> select t.*,p2(idx) from t where p2(idx)=25;
ID IDX NAME P2(IDX)
---------- ---------- -------------------- ----------
5 5 test 25
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID cz0uc8p8864nr, child number 0
-------------------------------------
select t.*,p2(idx) from t where p2(idx)=25
Plan hash value: 1228376738
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 11 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IF_T_IDX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00004$"=25)
--//可以发现可以使用索引.
3.删除函数P2呢?
SCOTT@test01p> drop function p2;
Function dropped.
SCOTT@test01p> select index_name,index_type,funcidx_status,STATUS from user_indexes where index_name='IF_T_IDX';
INDEX_NAME INDEX_TYPE FUNCIDX_ STATUS
-------------------- --------------------------- -------- -------
IF_T_IDX FUNCTION-BASED NORMAL DISABLED VALID
--//索引funcidx_status是disabled,而状态还有效.
SCOTT@test01p> select OWNER,OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,STATUS from dba_objects where object_name='IF_T_IDX' and owner=user;
OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE STATUS
----- ----------- ---------- -------------- ----------- ------
SCOTT IF_T_IDX 107046 107046 INDEX VALID
SCOTT@test01p> select * from dba_extents where owner=user and segment_name='IF_T_IDX';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
----- ------------ -------------- ------------ --------------- ---------- ------- -------- ----- ------ ------------
SCOTT IF_T_IDX INDEX USERS 0 9 9352 65536 8 9
--//索引段依旧存在没有删除.
--//也就是当函数删除时,其对应的索引状态disabled.这也就是为什么仅仅函数索引能disable.
SCOTT@test01p> alter index if_t_idx enable;
alter index if_t_idx enable
*
ERROR at line 1:
ORA-30550: index depends on a package/function spec/body which is not valid
--//函数不存在了,自然不能enable.
SCOTT@test01p> select index_name,index_type,funcidx_status,STATUS from user_indexes where index_name='IF_T_IDX';
INDEX_NAME INDEX_TYPE FUNCIDX_ STATUS
-------------------- --------------------------- -------- --------------------
IF_T_IDX FUNCTION-BASED NORMAL DISABLED VALID
--//试着插入数据看看.
SCOTT@test01p> insert into t values (6,6,'a');
insert into t values (6,6,'a')
*
ERROR at line 1:
ORA-30554: function-based index SCOTT.IF_T_IDX is disabled
--//这时无法插入.
4.重新建立函数P2:
CREATE FUNCTION p2 (x NUMBER)
RETURN NUMBER DETERMINISTIC IS
BEGIN
RETURN power(x,3);
END;
/
--//注意函数定义不再是power(x,2),power(x,3).
SCOTT@test01p> select index_name,index_type,funcidx_status,STATUS from user_indexes where index_name='IF_T_IDX';
INDEX_NAME INDEX_TYPE FUNCIDX_ STATUS
---------- --------------------- -------- -------
IF_T_IDX FUNCTION-BASED NORMAL DISABLED VALID
SCOTT@test01p> select t.*,p2(idx) from t ;
ID IDX NAME P2(IDX)
--- ---- ----- -------
1 1 test 1
2 2 test 8
3 3 test 27
4 4 test 64
5 5 test 125
5 rows selected.
--//OK,现在函数计算的power(idx,3).如果enable会修改函数索引的键值吗?当然现在还不能使用函数索引.
SCOTT@test01p> alter index if_t_idx enable;
Index altered.
--//^_^,这样就enable了.
SCOTT@test01p> select index_name,index_type,funcidx_status,STATUS from user_indexes where index_name='IF_T_IDX';
INDEX_NAME INDEX_TYPE FUNCIDX_ STATUS
---------- --------------------- -------- ------
IF_T_IDX FUNCTION-BASED NORMAL ENABLED VALID
SCOTT@test01p> delete from t where id=5;
delete from t where id=5
*
ERROR at line 1:
ORA-08102: index key not found, obj# 107047, file 9, block 9355 (2)
SCOTT@test01p> select OBJECT_ID,DATA_OBJECT_ID from dba_objects where object_name='IF_T_IDX' and owner=user;
OBJECT_ID DATA_OBJECT_ID
---------- --------------
107047 107047
--//说明找不到索引键值无法删除.直接报错.插入呢?
SCOTT@test01p> insert into t values (7,7,'b');
1 row created.
SCOTT@test01p> commit ;
Commit complete.
--//可以想象现在的索引记录的键值已经乱套了.
SCOTT@test01p> select t.*,p2(idx) from t where p2(idx)=25;
ID IDX NAME P2(IDX)
---------- ---------- -------------------- ----------
5 5 test 25
--//这里计算power(5,2).
SCOTT@test01p> select t.*,p2(idx) from t where p2(idx)=343;
ID IDX NAME P2(IDX)
---------- ---------- -------------------- ----------
7 7 b 343
SCOTT@test01p> select t.*,p2(idx) from t where p2(idx)=125;
no rows selected
SCOTT@test01p> select t.*,p2(idx) from t where p2(idx)=49;
no rows selected
4.当然解决问题也很简单:
--//重建函数索引就ok了,
--//顺便测试选择rebuild online是否可行.
SCOTT@test01p> alter index if_t_idx rebuild online ;
Index altered.
SCOTT@test01p> select t.*,p2(idx) from t where p2(idx)=25;
no rows selected
SCOTT@test01p> select t.*,p2(idx) from t where p2(idx)=125;
ID IDX NAME P2(IDX)
---------- ---------- -------------------- ----------
5 5 test 125
SCOTT@test01p> delete from t where id=7;
1 row deleted.
SCOTT@test01p> delete from t where id=3;
1 row deleted.
SCOTT@test01p> select t.*,p2(idx) from t where p2(idx)=49;
no rows selected
--//OK,现在正常.
--//当然一般在实践中很少建立这样的函数索引.