[20171202]关于函数索引的状态.txt

[20171202]关于函数索引的状态.txt

--//我曾经在一篇贴子提到索引可以disable吗?链接:

--//实际上仅仅函数索引能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> insert into t values (6,6,'a');

insert into t values (6,6,'a')

ORA-30554: function-based index SCOTT.IF_T_IDX is disabled

--//这时无法插入.

4.重新建立函数P2:

    RETURN power(x,3);

--//注意函数定义不再是power(x,2),power(x,3).

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会修改函数索引的键值吗?当然现在还不能使用函数索引.

Index altered.

--//^_^,这样就enable了.

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

IF_T_IDX   FUNCTION-BASED NORMAL ENABLED  VALID

SCOTT@test01p> delete from t where id=5;

delete from t where id=5

             *

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.

--//可以想象现在的索引记录的键值已经乱套了.

--//这里计算power(5,2).

SCOTT@test01p> select t.*,p2(idx) from t where p2(idx)=343;

         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;

4.当然解决问题也很简单:

--//重建函数索引就ok了,

--//顺便测试选择rebuild online是否可行.

SCOTT@test01p> alter index if_t_idx rebuild online ;

         5          5 test                        125

SCOTT@test01p> delete from t where id=7;

1 row deleted.

SCOTT@test01p> delete from t where id=3;

--//OK,现在正常.

--//当然一般在实践中很少建立这样的函数索引.