显式或隐式类型转换走INDEX FULL SCAN需要满足的条件

   我们知道，对于索引使用了函数（隐式或显式类型转换）会导致索引失效，但是如果查询的数据可以全部从索引中获取，可能走INDEX FULL SCAN或INDEX FAST FULL SCAN，但是，这是有条件的，具体见下面分析。

--OBJECT_ID 没有NOT NULL约束
Name                                                                                                              Null?    Type
 -----------------------------------------------------------------------------------------------
 OWNER                                                                                                                      VARCHAR2(30)
 OBJECT_NAME                                                                                                                VARCHAR2(128)
 SUBOBJECT_NAME                                                                                                             VARCHAR2(30)
 OBJECT_ID                                                                                                                  NUMBER
 DATA_OBJECT_ID                                                                                                             NUMBER
 OBJECT_TYPE                                                                                                                VARCHAR2(19)
 CREATED                                                                                                                    DATE
 LAST_DDL_TIME                                                                                                              DATE
 TIMESTAMP                                                                                                                  VARCHAR2(19)
 STATUS                                                                                                                     VARCHAR2(7)
 TEMPORARY                                                                                                                  VARCHAR2(1)
 GENERATED                                                                                                                  VARCHAR2(1)
 SECONDARY                                                                                                                  VARCHAR2(1)
 NAMESPACE                                                                                                                  NUMBER
 EDITION_NAME                                                                                                               VARCHAR2(30)
 


dingjun123@ORADB> !ora idxdesc t dingjun123
\n=============Wed Nov  4 12:37:36 CST 2020===================\n


Session altered.


Elapsed: 00:00:00.00


Session altered.


Elapsed: 00:00:00.01


Session altered.


Elapsed: 00:00:00.00


INDEX_NAME                       INDEX_COL                      INDEX_TYPE             PAR
-------------------------------- ------------------------------ ---------------------- ---
DINGJUN123.IDX_T                 OBJECT_ID                      NORMAL-NONUNIQUE       NO


1 row selected.


Elapsed: 00:00:00.07


--由于使用to_char(object_id)导致索引失效，但是查询的数据可以全部从索引中获取，因此可能走INDEX FULL SCAN或INDEX FAST FULL SCAN
--但是下面的语句增加HINTS也走全表扫描，具体原因后续分析。

dingjun123@ORADB> select/*+index_ffs(t idx_t)*/ object_id from t where to_char(object_id)='100';


1 row selected.


Elapsed: 00:00:00.04


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |   307   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     5 |   307   (1)| 00:00:04 |
--------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter(TO_CHAR("OBJECT_ID")='100')




Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1097  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


dingjun123@ORADB> delete from t where object_id is null;


0 rows deleted.


--增加NOT NULL约束，走INDEX FAST FULL SCAN

dingjun123@ORADB> alter table t modify object_id  not null;


Table altered.


Elapsed: 00:00:00.05
dingjun123@ORADB> set autotrace traceonly
dingjun123@ORADB> select object_id from t where to_char(object_id)='100';


1 row selected.


Elapsed: 00:00:00.02


Execution Plan
----------------------------------------------------------
Plan hash value: 2497555198


------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |     1 |     5 |    49   (3)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| IDX_T |     1 |     5 |    49   (3)| 00:00:01 |
------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter(TO_CHAR("OBJECT_ID")='100')




Statistics
----------------------------------------------------------
         29  recursive calls
          0  db block gets
        208  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed




--没有NOT NULL约束，只能全表扫描
dingjun123@ORADB> alter table t modify object_id null;


Table altered.


Elapsed: 00:00:00.01
dingjun123@ORADB> select object_id from t where to_char(object_id)='100';


1 row selected.


Elapsed: 00:00:00.02


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |   307   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     5 |   307   (1)| 00:00:04 |
--------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter(TO_CHAR("OBJECT_ID")='100')




Statistics
----------------------------------------------------------
         26  recursive calls
          0  db block gets
       1123  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed


--增加object_id is not null也可以走INDEX FAST FULL SCAN
dingjun123@ORADB> select object_id from t where to_char(object_id)='100' and object_id is not null;


1 row selected.


Elapsed: 00:00:00.01


Execution Plan
----------------------------------------------------------
Plan hash value: 2497555198


------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |     1 |     5 |    49   (3)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| IDX_T |     1 |     5 |    49   (3)| 00:00:01 |
------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter(TO_CHAR("OBJECT_ID")='100' AND "OBJECT_ID" IS NOT NULL)




Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        178  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

总结：
显式或隐式类型转换要走INDEX FULL SCAN，必须增加NOT NULL约束或显示条件增加IS NOT NULL才能走INDEX (FAST) FULL SCAN。
否则to_char(object_id)='100'这种条件只是告诉优化器，to_char(object_id)肯定IS NOT NULL，
但是优化器不知道里面的参数object_id是否为NULL，所以不增加NOT NULL约束或不增加条件，肯定走不了INDEX FAST FULL SCAN，使用HINTS都不行。