[20171202]關於函式索引的狀態.txt

[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,現在正常.
--//當然一般在實踐中很少建立這樣的函式索引.