[20190918]關於函式索引問題.txt

[20190918]關於函式索引問題.txt

1.環境：
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

create table t as select 1 id1, rownum id2 ,'test' name from dual connect by level<1e4;
insert into t values (1e4,1e4,'abcd');
commit ;
create index if_t_id2 on t(decode(id1, 1, to_number(null), id2));

--//分析表略。
--//簡單說明,使用to_number(null)保證返回資料型別是number型別的NULL值。

2.測試：
SCOTT@test01p> select column_name,data_type from user_tab_cols where table_name = 'T' ;
COLUMN_NAME          DATA_TYPE
-------------------- ----------
ID1                  NUMBER
ID2                  NUMBER
NAME                 CHAR
SYS_NC00004$         NUMBER
--//增加一個隱含欄位SYS_NC00004$.返回資料型別是number型別.

SCOTT@test01p> alter session set statistics_level = all;
Session altered.

SCOTT@test01p> select * from t where decode(id1, 1,to_number(null), id2) = 1e4;
       ID1        ID2 NAME
---------- ---------- --------------------
     10000      10000 abcd

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  7srfk2yjdxx49, child number 0
-------------------------------------
select * from t where decode(id1, 1,to_number(null), id2) = 1e4
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |    10 (100)|          |      1 |00:00:00.01 |      31 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |    100 |  1200 |    10   (0)| 00:00:01 |      1 |00:00:00.01 |      31 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(DECODE("ID1",1,NULL,"ID2")=10000)
--//你可以發現實際上filter(DECODE("ID1",1,NULL,"ID2")=10000).

SCOTT@test01p> select * from user_ind_expressions where index_name = 'IF_T_ID2'
  2  @prxx
==============================
INDEX_NAME                    : IF_T_ID2
TABLE_NAME                    : T
COLUMN_EXPRESSION             : DECODE("ID1",1,NULL,"ID2")
COLUMN_POSITION               : 1
PL/SQL procedure successfully completed.
--//你可以發現我建立的函式索引的表示式與儲存的不一致.

--//嘗試改寫看看呢?
SCOTT@test01p> select * from t where decode(id1, 1,null, id2) = 1e4;
       ID1        ID2 NAME
---------- ---------- --------------------
     10000      10000 abcd

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  by0609fp41hy2, child number 0
-------------------------------------
select * from t where decode(id1, 1,null, id2) = 1e4
Plan hash value: 1130968923
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T        |      1 |      1 |    13 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN                  | IF_T_ID2 |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------------------------------------------------
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$"=10000)

--//這樣可以使用索引,而這樣明視訊記憶體在1個錯誤,按照Yangtingkun以前blog介紹,這樣的返回型別是字元型.因為NULL沒有明確指定
--//預設型別是varchar2型別.而實際現在是number型別.是因為SYS_NC00004$是NUMBER型別.

3.繼續測試:
--//如果rebuild online索引呢?
SCOTT@test01p> alter index IF_T_ID2 rebuild online ;
Index altered.

SCOTT@test01p> select * from t where decode(id1, 1,null, id2) = 1e4;
       ID1        ID2 NAME
---------- ---------- --------------------
     10000      10000 abcd

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  by0609fp41hy2, child number 0
-------------------------------------
select * from t where decode(id1, 1,null, id2) = 1e4
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |    11 (100)|          |      1 |00:00:00.01 |      31 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |      1 |    13 |    11  (10)| 00:00:01 |      1 |00:00:00.01 |      31 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_NUMBER(DECODE("ID1",1,NULL,TO_CHAR("ID2")))=10000)
--//注意看過濾條件,發生了隱式轉換.前面加上了TO_NUMBER.

SCOTT@test01p> select column_name,data_type from user_tab_cols where table_name = 'T' ;
COLUMN_NAME          DATA_TYPE
-------------------- --------------------
ID1                  NUMBER
ID2                  NUMBER
NAME                 CHAR
SYS_NC00004$         VARCHAR2
--//重建索引後,隱含欄位SYS_NC00004$的資料型別對比前面的情況發生了變化,變為varchar2型別.
--//要保證使用索引應該寫成如下:

SCOTT@test01p> select * from t where decode(id1, 1,null, id2) = to_char(1e4);
       ID1        ID2 NAME
---------- ---------- --------------------
     10000      10000 abcd

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  63zyt23ufr2xa, child number 0
-------------------------------------
select * from t where decode(id1, 1,null, id2) = to_char(1e4)
Plan hash value: 1130968923
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T        |      1 |      1 |    13 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN                  | IF_T_ID2 |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------------------------------------------------
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$"='10000')