[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')