substr函数索引创建测试
技术群里小伙伴，沟通说一条经常查询的SQL缓慢，单表SQL一个列作为条件，列是int数值类型，索引类型默认创建。
一.SQL文本
substr函数索引创建测试 select *from(select substr(nm,0,17) nm1 from bbm2019) where nm1 in ('55552389655808973')
需求，将上述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,0,4) cc from tt) t where t.cc in ('2559'); -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 870 | 4350 | 347 (1)| 00:00:05 | |* 1 | TABLE ACCESS FULL| TT | 870 | 4350 | 347 (1)| 00:00:05 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(SUBSTR(TO_CHAR("OBJECT_ID"),0,4)='2559') 调整后，使用单行SQL查询，不使用子查询 SQL> select substr(object_id,0,4) from tt where substr(object_id,0,4) in ('2559'); -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 870 | 4350 | 347 (1)| 00:00:05 | |* 1 | TABLE ACCESS FULL| TT | 870 | 4350 | 347 (1)| 00:00:05 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(SUBSTR(TO_CHAR("OBJECT_ID"),0,4)='2559') 无效，SQL还是一次全表扫描，只是测试使用。
2.2 调整索引为字符格式，SQL访问使用%模糊匹配 select substr(nm,0,17) 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 | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 182 | 347 (1)| 00:00:05 | |* 1 | TABLE ACCESS FULL| TT | 14 | 182 | 347 (1)| 00:00:05 | --------------------------------------------------------------------------
可以发现，当表字段为数值类型，使用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 | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 35 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TT | 1 | 35 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TT_OBJE_IND | 1 | | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- SQL> select object_id from tt where object_id like '2559%'; ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 385 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TT | 11 | 385 | 5 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TT_OBJE_IND | 11 | | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------------------
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='25599'; SQL> select object_id from tt where object_id in '25599'; SQL> select substr(object_id,0,4) from tt where object_id in('2559'); -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 490 | 347 (1)| 00:00:05 | |* 1 | TABLE ACCESS FULL| TT | 14 | 490 | 347 (1)| 00:00:05 | --------------------------------------------------------------------------
发现使用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,0,4));
SQL> select substr(object_id,0,4) from tt where substr(object_id,0,4) in ('2559');
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 110 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TT_OBJE_IND | 11 | 110 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(SUBSTR(TO_CHAR("OBJECT_ID"),0,4)='2559')
SQL> create index tt_obje_ind on tt(substr(to_char(object_id),0,4));
SQL> select substr(object_id,0,4) from tt where substr(object_id,0,4) in ('2559');
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 110 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TT_OBJE_IND | 11 | 110 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(SUBSTR(TO_CHAR("OBJECT_ID"),0,4)='2559')
创建substr函数索引，oracle自动会to_char转换，可以显示创建索引语法加上 （可不加,规范语法加上最好~)
on tt(substr(to_char(object_id),0,4));
