技术群里小伙伴，沟通说一条经常查询的SQL缓慢，单表SQL一个列作为条件，列是int数值类型，索引类型默认创建。
一.SQL文本
substr函数索引创建测试
select *from(select substr(nm,,) nm1 from bbm2019) where nm1 in ('')
需求，将上述SQL执行速度加快，目的是走索引。
创建测试表
SQL>create table tt as select * from dba_objects;
SQL> desc tt
OBJECT_ID NUMBER
二.优化思路
2.1 通过修改SQL文本方式
调整前
SQL> select * from (select substr(object_id,,) cc from tt) t where t.cc in ('');
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| | SELECT STATEMENT | | | | ()| :: |
|* | TABLE ACCESS FULL| TT | | | ()| :: |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
- filter(SUBSTR(TO_CHAR("OBJECT_ID"),,)='')
调整后，使用单行SQL查询，不使用子查询
SQL> select substr(object_id,,) from tt where substr(object_id,,) in ('');
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| | SELECT STATEMENT | | | | ()| :: |
|* | TABLE ACCESS FULL| TT | | | ()| :: |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
- filter(SUBSTR(TO_CHAR("OBJECT_ID"),,)='')
无效，SQL还是一次全表扫描，只是测试使用。 2.2 调整索引为字符格式，SQL访问使用%模糊匹配
select substr(nm,,) nm1 lrrq from bbm2019 where nm1in like '55552389655808973%';
SQL> create index tt_obje_ind on tt(object_id);
SQL> set autotrace on
SQL>select object_id from tt where object_id like '25599%';
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| | SELECT STATEMENT | | | | ()| :: |
|* | TABLE ACCESS FULL| TT | | | ()| :: |
--------------------------------------------------------------------------
可以发现，当表字段为数值类型，使用like 字符格式访问，是无法获取结果的。
SQL> drop index tt_obje_ind;
SQL> create index tt_obje_ind on tt(to_char(object_id));
SQL> select object_id from tt where object_id like '25599%';
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| | SELECT STATEMENT | | | | ()| :: |
| | TABLE ACCESS BY INDEX ROWID| TT | | | ()| :: |
|* | INDEX RANGE SCAN | TT_OBJE_IND | | | ()| :: |
-------------------------------------------------------------------------------------------
SQL> select object_id from tt where object_id like '2559%';
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| | SELECT STATEMENT | | | | ()| :: |
| | TABLE ACCESS BY INDEX ROWID| TT | | | ()| :: |
|* | INDEX RANGE SCAN | TT_OBJE_IND | | | ()| :: |
-------------------------------------------------------------------------------------------
2 - access(TO_CHAR("OBJECT_ID") LIKE '2559%')
filter(TO_CHAR("OBJECT_ID") LIKE '2559%')
使用to_char可以将索引存储格式调整为字符类型，where条件使用%进行查询可以走索引快速访问。
SQL> select object_id from tt where object_id='';
SQL> select object_id from tt where object_id in ''; SQL> select substr(object_id,,) from tt where object_id in('');
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| | SELECT STATEMENT | | | | ()| :: |
|* | TABLE ACCESS FULL| TT | | | ()| :: |
--------------------------------------------------------------------------
发现使用in =并未走索引！
1 - filter("OBJECT_ID"=25599) 可以发现并未显示to_char
此处，第一次发现oracle隐患转换的优先级，可能会影想是否走索引，由于oracle to_number优先级大于to_char因此即使我们写法'xx'字符数值等值查询，
oracle自动转换为数值类型，由于索引为字符类型，因此无法走索引。
SQL> select object_id from tt where to_char(object_id)='25599';
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 870 | 23490 | 72 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TT | 870 | 23490 | 72 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TT_OBJE_IND | 348 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(TO_CHAR("OBJECT_ID")='25599')
不创建函数索引，直接使用to_char类，查询条件 2.3 创建Substr函数索引
SQL> drop index tt_obje_ind;
SQL> create index tt_obje_ind on tt(substr(object_id,,));
SQL> select substr(object_id,,) from tt where substr(object_id,,) in ('');
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| | SELECT STATEMENT | | | | ()| :: |
|* | INDEX RANGE SCAN| TT_OBJE_IND | | | ()| :: |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
- access(SUBSTR(TO_CHAR("OBJECT_ID"),,)='')
SQL> create index tt_obje_ind on tt(substr(to_char(object_id),,));
SQL> select substr(object_id,,) from tt where substr(object_id,,) in ('');
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| | SELECT STATEMENT | | | | ()| :: |
|* | INDEX RANGE SCAN| TT_OBJE_IND | | | ()| :: |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
- access(SUBSTR(TO_CHAR("OBJECT_ID"),,)='')
创建substr函数索引，oracle自动会to_char转换，可以显示创建索引语法加上 （可不加,规范语法加上最好~)
on tt(substr(to_char(object_id),0,4));