--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都不行。