[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,現在正常.

--//當然一般在實踐中很少建立這樣的函數索引.